Building dynamic SQL query 3

mais123 · Jan 25, 2008

Hi, I am trying to build a dynamic SQL query that does customer search. It accepts a bunch of parameters and dynamically creates select statement based on whether eaach parameter is null.
So say I have this
Create Procedure SearchCustomer
(
@CustomerName varchar(50),
@CustomerAddress varchar(100),
@Phone varchar(50
)
AS

Here I need to evaluate which parameter has value and create a statement based on that. So if say they pass customername and phone my dynamic statement needs to be
@Statement =
Select * from Customer where CustomerName=@CustomerName and Phone = @Phone
And then of course I probably need to dom something like
Exec(@Statement)
So what are the best practices of doing these things, especially when it comes to building statements and placing all the "AND"s and properly enclosing in quotes, etc

Thanks for any help!

RiverGuy · Jan 25, 2008

Why don't you forgo the Dynamic SQL and just change your statement?

Code:

IF (@CustomerName IS NOT NULL OR @CustomerAddress IS NOT NULL OR @Phone IS NOT NULL)
SELECT * FROM Customer WHERE (CustomerName = @CustomerName OR @CustomerName IS NULL) AND (CustomerAddress = @CustomerAddress @CustomerAddress IS NULL) AND (Phone = @Phone OR @Phone IS NULL)

gmmastros · Jan 25, 2008

Code:

Create Procedure SearchCustomer
(
@CustomerName varchar(50),
@CustomerAddress varchar(100),
@Phone varchar(50)
)
AS
Set NOCOUNT On
Set @CustomerName = Coalesce(@CustomerName, '')
Set @CustomerAddress = Coalesce(@CustomerAddress , '')
Set @Phone = Coalesce(@Phone , '')

Select Columns...
From   Table
Where  (@CustomerName = '' Or CustomerName = @CustomerName)
       And (@CustomerAddress = '' Or CustomerAddress = @CustomerAddress)
       And (@Phone = '' Or Phone = @Phone)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RiverGuy · Jan 25, 2008

Forgot an OR

Code:

IF (@CustomerName IS NOT NULL OR @CustomerAddress IS NOT NULL OR @Phone IS NOT NULL)
SELECT * FROM Customer WHERE (CustomerName = @CustomerName OR @CustomerName IS NULL) AND (CustomerAddress = @CustomerAddress [b]OR[/b] @CustomerAddress IS NULL) AND (Phone = @Phone OR @Phone IS NULL)

jordanking · Jan 25, 2008

you want to avoid dynamic sql if possible (debatable). But if you want to use it you need to protect against sql injection and other problems.

here is a dynamic sql serch i use for customer records:

based on sql server 2005

Code:

CREATE PROCEDURE [dbo].[ps_Customer_SEARCH]
---Declare input variables
                 @OrderBy		nvarchar(50)= NULL,		--column name to odrder by
				 @OrderByDir	nvarchar(1) = NULL,		--Direction of sort 'A' = ascending 'D' = Descending
				 @CustomerID	int			= NULL,  
                 @CompanyID		int			= NULL,   
                 @FirstName		nvarchar(50)= NULL,
                 @LastName		nvarchar(50)= NULL,  
                 @Billing		nvarchar(50)= NULL, 
                 @AccountStatus int		    = NULL, 
                 @SDBID			int			= NULL, 
                 @ERUserID		int			= NULL,  
                 @Erequest		bit			= NULL,  
                 @Mail			bit			= NULL,
				 @Created		datetime	= NULL,
                 @Name			nvarchar(50)= NULL,
                 @Branch		nvarchar(50)= NULL,
				 @LocalRows		int			= 0 OUTPUT,
				 @ReturnValue	int			= 0 OUTPUT,
				 @LocalError	int			= 0 OUTPUT,
				 @OutMessage	nvarchar(500) OUTPUT   
AS
BEGIN TRY
	---SET NOCOUNT ON added to prevent extra result sets from
	---interfering with SELECT statements.
	SET NOCOUNT ON;
	---Declare local scope variables
	DECLARE @sql        nvarchar(4000),
			@paramlist  nvarchar(4000)

	---Create the SQL string that will be dynamic
	SELECT @sql =	'SELECT dbo.Customer.intCustomerID, dbo.Customer.intCompanyID, dbo.Customer.chrFirstName, 
							dbo.Customer.chrLastName, dbo.Customer.chrBilling, dbo.Customer.intAccountStatus, dbo.Customer.intSDBID, 
							dbo.Customer.intERUserID, dbo.Customer.blnErequest, dbo.Customer.blnMail, dbo.Customer.dtCreated, 
							dbo.Customer.intRecCount, dbo.Company.chrName, dbo.Company.chrBranch, dbo.AccountStatus.chrStatus, 
							dbo.Customer.chrLastName + '', '' + dbo.Customer.chrFirstName AS chrCustomer,
							dbo.Company.chrName + '' '' + ISNULL(dbo.Company.chrBranch, '''') AS chrCompany						
					FROM
							dbo.Company
							INNER JOIN dbo.Customer ON dbo.Company.intCompanyID = dbo.Customer.intCompanyID
							INNER JOIN dbo.AccountStatus ON dbo.Customer.intAccountStatus = dbo.AccountStatus.intAccountStatusID
					WHERE  1 = 1'

	---Use is null to determin if parameter was passed, if so append to SQL string...
	IF @CustomerID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intCustomerID = @CustomerID'

	IF @CompanyID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intCompanyID = @CompanyID'

	IF @FirstName IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.chrFirstName LIKE ''%'' + @FirstName + ''%''' 

	IF @LastName IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.chrLastName LIKE ''%'' + @LastName + ''%''' 

	IF @Billing IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.chrBilling LIKE ''%'' + @Billing + ''%''' 

	IF @AccountStatus IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intAccountStatus = @AccountStatus'

	IF @SDBID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intSDBID = @SDBID'

	IF @ERUserID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intERUserID = @ERUserID'

	IF @Erequest IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.blnErequest = @Erequest'

	IF @Mail IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.blnMail = @Mail'
	 
	IF @Created IS NOT NULL
	   SELECT @sql = @sql + ' AND dbo.Customer.dtCreated >= @Created AND dbo.Customer.dtCreated < @Created + 1' --Selects records created after this date

	IF @Name IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Company.chrName LIKE ''%'' + @Name + ''%''' 

	IF @Branch IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Company.chrBranch LIKE ''%'' + @Branch + ''%''' 

	---Set the order by staement
	IF @OrderBy IS NOT NULL
		SELECT @sql = @sql + ' ORDER BY' +
		CASE
			WHEN @OrderBy = 'FirstName'		AND @OrderByDir = 'D'	THEN ' dbo.Customer.chrFirstName DESC'
			WHEN @OrderBy = 'FirstName'		AND @OrderByDir != 'D'	THEN ' dbo.Customer.chrFirstName'
			WHEN @OrderBy = 'LastName'		AND @OrderByDir = 'D'	THEN ' dbo.Customer.chrLastName DESC'
			WHEN @OrderBy = 'LastName'		AND @OrderByDir != 'D'	THEN ' dbo.Customer.chrLastName'
			WHEN @OrderBy = 'Billing'		AND @OrderByDir = 'D'	THEN ' dbo.Customer.chrBilling DESC'
			WHEN @OrderBy = 'Billing'		AND @OrderByDir != 'D'	THEN ' dbo.Customer.chrBilling'
			WHEN @OrderBy = 'SDBID'			AND @OrderByDir = 'D'	THEN ' dbo.Customer.intSDBID DESC'
			WHEN @OrderBy = 'SDBID'			AND @OrderByDir != 'D'	THEN ' dbo.Customer.intSDBID'
			WHEN @OrderBy = 'ERUserID'		AND @OrderByDir = 'D'	THEN ' dbo.Customer.intERUserID DESC'
			WHEN @OrderBy = 'ERUserID'		AND @OrderByDir != 'D'	THEN ' dbo.Customer.intERUserID'
			WHEN @OrderBy = 'AccountStatus' AND @OrderByDir = 'D'	THEN ' dbo.Customer.intAccountStatus DESC'
			WHEN @OrderBy = 'AccountStatus'	AND @OrderByDir != 'D'	THEN ' dbo.Customer.intAccountStatus'
			WHEN @OrderBy = 'Created'		AND @OrderByDir = 'D'	THEN ' dbo.Customer.dtCreated DESC'
			WHEN @OrderBy = 'Created'		AND @OrderByDir != 'D'	THEN ' dbo.Customer.dtCreated'
			---Use default sort order for returned recordset.
			ELSE ' dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
		END
	ELSE
		---Use default sort order for returned recordset.
		SELECT @sql = @sql + ' ORDER BY dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
	----Set the parameter list for the sp_executesql call
	 SELECT @paramlist =   '@CustomerID		int,  
							@CompanyID		int,   
							@FirstName		nvarchar(50),
							@LastName		nvarchar(50),  
							@Billing		nvarchar(50), 
							@AccountStatus	int, 
							@SDBID			int, 
							@ERUserID		int,  
							@Erequest		bit,  
							@Mail			bit,
							@Created		datetime,
							@Name			nvarchar(50),
							@Branch			nvarchar(50)'

	---Execute the function sp_executesql that takes the following parameters: 
	---	sql string, parameter list, and then the declared parameters in the same order as the parmaeter list
	EXEC sp_executesql	@sql, @paramlist,
						@CustomerID, @CompanyID, @FirstName, @LastName, @Billing, @AccountStatus, 
						@SDBID, @ERUserID, @Erequest, @Mail, @Created, @Name, @Branch
	---if no errors, set the return value to 0 and return the number of rows in the search results
	SELECT @LocalRows = @@ROWCOUNT, @ReturnValue = 0
	SELECT @LocalRows, @ReturnValue
END TRY
BEGIN CATCH
	---If errors occured - capture error information and return to calling application
	SELECT @ReturnValue = 1, @LocalError = ERROR_NUMBER(), @OutMessage = ('ROWS AFFECTED = ' + CAST(@@ROWCOUNT as nvarchar(6)) + ' SEVERITY = ' + CAST(ERROR_SEVERITY() as nvarchar(3)) + ' STATE = ' + CAST(ERROR_STATE() as nvarchar(2)) + ' PROCEDURE = ' + ERROR_PROCEDURE() + ' LINE = ' + CAST(ERROR_LINE() as nvarchar(3)) + ' MESSAGE ' + ERROR_MESSAGE())
	SELECT @LocalError, @ReturnValue, @OutMessage
END CATCH

.....
I'd rather be surfing

SQLSister · Jan 25, 2008

Debateable? How is less secure and slower debatable? So you can save development time? And frankly I don't see where you save that in maintenance time. It is much harder to really test and debug and maintain this kind of code.

"NOTHING is more important in a database than integrity." ESquared

jordanking · Jan 25, 2008

SQWsister,

I agree, It should be avoided at all costs, it is slower, and harder to monitor/maintain. That being said, how do you approach dynamic searches like this?

.....
I'd rather be surfing

SQLSister · Jan 25, 2008

The where clause building would be handled as above.

The order by is trickier. You could put together a separate select for each order by possibility and use if statements to pick which one to run. That would still be a pain to maintain but so is the dynamic one, so really that's kind of a wash.

But by prefernce, I would see if there was a way to send the data set unordered to the application and have the application sort the data set.

It's late Friday afternoon and I'm tired so I'm probably not seeing something obvious, but maybe George or Dennis or Eric can think of a slick way to avoid all those order by clauses.

"NOTHING is more important in a database than integrity." ESquared

jordanking · Jan 25, 2008

thanks SQLsister,

this is an issue i have spent a lot of time "googling".
I have not been able to find a way to totally avoid dynamic sql like this for searches, without creating loads of individual queries.

.....
I'd rather be surfing

gmmastros · Jan 25, 2008

UH.... thanks SQLSister!

I'm hoping Erik can improve on this. [wink]

So... I worked up a little example. It seems to work, but I somehow doubt that the performance will be acceptable. This query will only work with SQL Server 2005 (and above) because it uses Row_Number to get a column that can be used to order the data. Now... it's a little weird because I use it twice. Once to order the data sequentially (ordered ASC) and again to number the rows in descending order.

Then, I use an Int column for the direction. If you want to sort ascending, set @Direction = 1. For Descending, Set @Direction = 0.

Then, I use the fact that you can specify an integer in the order by that represents the column number to order on. The sort is Always Ascending, then Descending. However, if you want Descending, you simply multiply the first column by 0, which renders it useless for sorting, and in that case, the second column takes over.

Truth is... I wanted to see if it could be done. I'm not necessarily proud of this code, and I somehow doubt that I would even put this in to one of my DB's.

Honestly... if I ever wanted to give my users this functionality I would return the data from the SP sorted by whatever column they wanted, and then when I present the data to the users, I would check the 'descending' flag and simply reverse the order in which I display the data.

Anyway... In case anyone is interested in the actual code...

Code:

Declare @Temp Table (Id Int Identity(1,1), ShoeSize numeric(3,1), Eyecolor varchar(10))

Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(9, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(10, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(10.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')

Declare @OrderColumn VarChar(20)
Declare @Direction int

[!]Set @OrderColumn = 'Eyecolor'
Set @Direction = 0[/!]

Select @Direction * Row_Number() Over (Order By Case @OrderColumn 
         When 'ShoeSize' Then Right('00000' + Convert(VarChar(10), ShoeSize), 4)
         When 'EyeColor' Then EyeColor
       End) As Sort1,
       Row_Number() Over (Order By Case @OrderColumn 
         When 'ShoeSize' Then Right('00000' + Convert(VarChar(10), ShoeSize), 4)
         When 'EyeColor' Then EyeColor
       End DESC) As Sort2,
		*
From   @Temp
Order By 1, 2

Feel free to play around with this a bit. To see this in action, copy/paste to SQL Server Management Studio and run it. Then, change @OrderColumn to 'ShoeSize', and @Direction = 1.

Again....

1. Only works with SQL Server 2005.
2. Interesting from an academic perspective, but probably not worthy of putting in to production.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Jan 25, 2008

This is slightly better.

Code:

Declare @Temp Table (Id Int Identity(1,1), ShoeSize numeric(3,1), Eyecolor varchar(10))

Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(9, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(10, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(10.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')

Declare @OrderColumn VarChar(20)
Declare @Direction int

[!]Set @OrderColumn = 'Eyecolor'
Set @Direction = -1[/!]

Select @Direction * Row_Number() Over (Order By Case @OrderColumn 
         When 'ShoeSize' Then Right('00000' + Convert(VarChar(10), ShoeSize), 4)
         When 'EyeColor' Then EyeColor
       End) As Sort1,
		*
From   @Temp
Order By 1

Instead of using 1 or 0 for the @Direction variable. Use 1 or -1 for it instead.

With this method, you multiply the rownumber values by -1 and then then always sort ascending. This allows you to ONLY use Row_Number() once.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

ESquared · Jan 25, 2008

Code:

Declare @Temp Table (Id Int Identity(1,1), ShoeSize numeric(3,1), Eyecolor varchar(10))

Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(9, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(10, 'Blue')
Insert Into @Temp(ShoeSize, EyeColor) Values(10.5, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Green')
Insert Into @Temp(ShoeSize, EyeColor) Values(8, 'Brown')
Insert Into @Temp(ShoeSize, EyeColor) Values(8.5, 'Brown')

Declare @OrderColumn VarChar(20)
Declare @Direction int

Set @OrderColumn = 'Eyecolor'
Set @Direction = -1

select * from @temp
order by
   coalesce(case when @ordercolumn = 'eyecolor' and @direction = 1 then null else '' end, eyecolor),
   coalesce(case when @ordercolumn = 'eyecolor' and @direction = -1 then null else '' end, eyecolor) desc,
   coalesce(case when @ordercolumn = 'shoesize' and @direction = 1 then null else 0 end, shoesize),
   coalesce(case when @ordercolumn = 'shoesize' and @direction = -1 then null else 0 end, shoesize) desc

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

ESquared · Jan 25, 2008

lol I am very silly

Code:

select * from @temp
order by
   case when @ordercolumn = 'eyecolor' and @direction = 1 then eyecolor else '' end,
   case when @ordercolumn = 'eyecolor' and @direction = -1 then eyecolor else '' end desc,
   case when @ordercolumn = 'shoesize' and @direction = 1 then shoesize else 0 end,
   case when @ordercolumn = 'shoesize' and @direction = -1 then shoesize else 0 end desc

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Building dynamic SQL query 3

mais123

Programmer

RiverGuy

Programmer

gmmastros

Programmer

RiverGuy

Programmer

jordanking

Programmer

SQLSister

Programmer

jordanking

Programmer

SQLSister

Programmer

jordanking

Programmer

gmmastros

Programmer

gmmastros

Programmer

ESquared

Programmer

ESquared

Programmer

Similar threads

Part and Inventory Search

Sponsor