SP with input parameters - dynamic query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP with input parameters - dynamic query

Author

Topic

bjones84
Starting Member

5 Posts

Posted - 2010-02-22 : 12:24:03

I am trying to create a SP for a report that will have as many as 10 input parameters. I am trying to create a dynamic SQL statement based on the values input. I could easily do this if I was in the VB world, but how do I dynamically build the SQL statement in the SP? Here is what I have so far, but am still getting errors:

Declare @SQL1 as varchar(MAX),
@FName varchar(60),
@LName varchar(60),
@strSearch varchar(MAX)

Set @strSearch = '(Shipments.sSUID IS NOT NULL)'

--Hardcoded for testing only
--Set @FName = 'John'
--Set @LName = 'Smith'

If @FName <> '' Begin
Set @strSearch = @strSearch + ' AND (Shipments.sFName = @FName )'
End

If @LName <> '' Begin
Set @strSearch = @strSearch + ' And (Shipments.sLName = @LName )'
End

Set @SQL1 = 'SELECT Shipments.sSUID, Shipments.sCompanyName AS CompanyName, Shipments.sFName AS FName, Shipments.sMName AS MName, Shipments.sLName AS LName,
Shipments.sAddress1 AS Address1, Shipments.sAddress2 AS Address2, Shipments.sCity AS City, Shipments.sState AS State,
Shipments.sPostalCode AS PostalCode, Shipments.sCountry AS Country, Shipments.sDateReceived AS BeginDateReceived,
Shipments.sDateReceived AS EndDateReceived, Contents.cContents AS Contents, Contents.cAmount AS Amount,
Contents.cCheckNumber AS CheckNumber, Contents.cQuantity AS ContentsQuantity, Contents.cNotes AS ContentsNotes,
Contents.cDateProcessed AS DateProcessed, Shipments.sPO AS PO, Packages.pTrackingNumber AS TrackingNumber,
Packages.pBOLNumber AS BOLNumber, Packages.pProductCode AS ProductCode, Packages.pQuantity AS PkgQuantity,
Packages.pPallets AS Pallets, Packages.pBoxes AS Boxes, Packages.pNotes AS PkgNotes, Packages.pDateEntered AS DateEntered
FROM Contents RIGHT OUTER JOIN
Packages ON Contents.cPUID = Packages.pPUID RIGHT OUTER JOIN
Shipments ON Packages.pSUID = Shipments.sSUID
GROUP BY Shipments.sSUID, Shipments.sCompanyName, Shipments.sFName, Shipments.sMName, Shipments.sLName, Shipments.sAddress1,
Shipments.sAddress2, Shipments.sCity, Shipments.sState, Shipments.sPostalCode, Shipments.sCountry, Shipments.sDateReceived,
Contents.cContents, Contents.cAmount, Contents.cCheckNumber, Contents.cQuantity, Contents.cNotes, Contents.cDateProcessed,
Shipments.sPO, Packages.pTrackingNumber, Packages.pBOLNumber, Packages.pProductCode, Packages.pQuantity, Packages.pPallets,
Packages.pBoxes, Packages.pNotes, Packages.pDateEntered
Having '

Exec (@SQL1 + @strSearch)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 12:39:24

you dont need dynamic sql here. you just need conditional where clause like


...
WHERE (Shipments.sFName = @FName OR @FName ='')
AND (Shipments.sLName = @LName OR @LName = '')
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bjones84
Starting Member

5 Posts

Posted - 2010-02-22 : 12:51:56

The problem is that the report will have 10 optional parameters. I need to determine which ones were entered and only build a SQL statement based on those. For instance, just using FirstName and LastName, they could enter just one or the other or they could enter both (Could be AND or OR). If there is no entry for a field, I don't want to include it. That was the reason for the dynamic SQL and the IF statements. In order to account for all scenarios it gets really complicated, when you start thinking about 10 possible parameter fields. I hope that explains the input a little better.

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 13:12:27

Dynamic SQL has lot of shortcomings.
With what Visakh has suggested will work for n number of parameters.
WHERE (Shipments.sFName = @FName OR @FName IS NULL)
AND (Shipments.sLName = @LName OR @LName IS NULL)
..
..
AND (10thFieldName = @10thParameter OR @10thParameter IS NULL)
Just pass the parameter values as NULL which are not selected from your Data access layer .

PBUH

bjones84
Starting Member

5 Posts

Posted - 2010-02-22 : 13:27:05

Sorry, but when using the where statement:

WHERE (Shipments.sFName = @FName OR @FName IS NULL)
AND (Shipments.sLName = @LName OR @LName IS NULL)

It is looking for FName of a specific value OR NULL
AND
LName of a specific value OR NULL.

The problem is that if I don't have a FName value and only enter a LName value, it only pulls data where LName has a value AND FName is null. If FName field in the database contains any data, with will not pull it. Ex.

LName = Smith
FName = Joe

If in the parameters I only enter LName as Smith, this where clause will not pull the record for Joe Smith.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 13:33:00

quote:
It is looking for FName of a specific value OR NULL
AND
LName of a specific value OR NULL.

Incorrect...It doesn't check if FName field in the table is NULL..it only checks if the variable @FName is NULL (which it should be ...if you haven't entered anything)...Did you try running the query?

bjones84
Starting Member

5 Posts

Posted - 2010-02-22 : 13:40:12

Yes, I have run the query and it pulls back no data.

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 13:40:35

try this one.Put the values & try.


declare @tbl as table(fname varchar(40),lname varchar(40))
insert into @tbl
select 'Joe','Smith' union all
select 'mike','Smith'

declare @Fname as varchar(20)=NULL
declare @Lname as varchar(20)=NULL
select @Lname='Smith'

select * from @tbl t
 where (@FName IS NULL) OR (t.FName = @FName )
AND (@Lname IS NULL) OR (t.lname = @Lname )

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 13:42:34

What's the value @FName will have ?...will it be NULL or an empty string ('')..if its an empty string..use like how Visakh suggested..

WHERE (Shipments.sFName = @FName OR @FName ='')
AND (Shipments.sLName = @LName OR @LName = '')

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 13:48:24

For me all the scenarios are working.Even with fname null or lname null


declare @tbl as table(fname varchar(40),lname varchar(40))
insert into @tbl
select 'Joe','Smith' union all
select 'mike','Smith' union all
select 'john',NUll union all
select NUll,'Howard' 

declare @Fname as varchar(20)=NULL
declare @Lname as varchar(20)=NULL
select @Lname='Howard'


select * from @tbl t
 where (t.FName = @FName OR @FName IS NULL )
AND (t.LName = @LName OR @LName IS NULL  )

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:34:32

I think problem is OP is using '' as default value rather than NULL. thats why if you refer to my original suggestion I used comparison with '' rather than NULL
In any case below should work


...
WHERE (Shipments.sFName = @FName OR NULLIF(@FName,'') IS NULL)
AND (Shipments.sLName = @LName OR NULLIF(@LName,'') IS NULL)
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bjones84
Starting Member

5 Posts

Posted - 2010-02-23 : 10:50:44

Yes, that works. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:59:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources