Dynamic sql where in sql server

I’ve been recently working a dynamic sql script launched from within a stored procedure that will generate a filter claused based on parameters passed. One the filter clause that i had to take into account was a “WHERE IN”, while passing something like “a,b,c” as parameter, it did not seem to work, so i went a bit in depth and found out that there was some issues while using the WHERE IN clause in dynamic SQL.

The solution found to this problem is to transform the list in the parameter into a table form that can be used in the dynamic WHERE IN clause

[sql]

DECLARE @ParamList NVARCHAR(max)

DECLARE @Query NVARCHAR(4000)

SET @ParamList = ‘hello,this,is,a,list,of,param’

SET @Query = N’SELECT * FROM TableName WHERE ColumnName IN (SELECT param FROM iter_simple_charlist_to_tbl(@ParamList))’

exec sp_executesql @Query, N’@ParamList NVARCHAR(max)’, @ParamList

[/sql]

 

Below is the script for the Table-valued function to convert a given VARCHAR parameter list to table (A small variation is needed for integer type parameter list)

[sql]
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[iter_simple_charlist_to_tbl]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION [dbo].[iter_simple_charlist_to_tbl]
GO
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[iter_simple_charlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (param nvarchar(50) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(‘,’, @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END – @pos – 1
INSERT @tbl (param)
VALUES (substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
[/sql]

References of this implementation are:
http://www.sommarskog.se/arrays-in-sql-2005.html#returnformat
http://www.restfuldevelopment.net/david-kawliche/writing/dynamic-in-without-dynamic-sql/

Note: This solution does not offer an performance optimization and can be inefficient in some scenarios.

Paging large result sets with SQL query

I’ve been searching on the internet for several ways to perform paging on a large result set from an SQL Query, there are several solutions, that can be found some are custom and others are already provided with ASP.NET Controls. This is the solution i have been using for performing paging of large datasets for data that is being used on a current web 2.0 site.

Here is a common problem:
A website offering search capabilities for it’s users is having performance issues while getting the result from it’s database, they have more than 100K record, and users are able to perform criteria search or just a text search on it. Criteria search can take up a lot of time to process depending on how it has been implemented. Statistics have shown that the average result count is about 1000 record for text search and 500 for criteria search.

The Stored Procedure
Let’s take this SQL Query as a sample query to get result for a typical text search.

SELECT Id, ProductName, ProductDescription, ProductRating, ProductPrice
FROM tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'

This stored procedure takes one parameter a query ( @Query ), that the website visitor types in to search for something. And the query search both the Product Name and Product Description Field. This may result in some huge result set for some key terms.

We need to optimize this query to be able to perform custom paging on the other side.

here is how the optimized code would be:

--Will calculate the number of records of this particular request
SELECT @RecordCount = COUNT(1)
FROM
tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'

SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS Row, Id, ProductName, ProductDescription, ProductRating, ProductPrice
FROM
tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'
) AS TmpTbl
WHERE Row BETWEEN @RowStart AND @RowEnd
ORDER BY ProductRating

I have added 2 input parameters which are the @RowStart and @RownEnd, and 1 output parameter which is @RecordCount. With these new parameters added you will be able to lauch a query based on the number of records you want to display on a particular page. If you want to display 10 records per page your input parameters will have the following values
@RowStart = 1
@RowEnd = 10
since the query is inclusive.

You can use your prefered data control to display the data, and to build up the Paging logic in ASP.NET