not-always-passed parameters - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

not-always-passed parameters

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-24 : 09:45:47

I am creating a stored procedure that will have two parameters. One of the two will always be passed. Whichever one is passed should be used in the where clause of a select statement. Can I do this without a dynamic sql string?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-24 : 10:03:54

If the params both have a default of NULL, then you can do this.

WHERE Field = @Param1 OR Field = @Param2

This is because the test NULL = NULL will return a false.

There are 10 types of people in the world, those that understand binary, and those that don't.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 10:45:04

Are the two parameters used for two different columns in your where clause?

Then do this:
where @parm1 is null or field1 = @parm1
and @parm2 is null or field2 = @parm2

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:38:46

[code]where field1 = coalesce(@parm1,field1)
and field2 = coalesce(@parm2,field2)[/code]

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

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-24 : 12:07:53

Awesome. Thanks guys.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 12:11:10

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-30 : 01:52:39

quote:
Originally posted by visakh16

where field1 = coalesce(@parm1,field1)
and field2 = coalesce(@parm2,field2)

What if the second field is not necessarily null.

Basically I have the application server user identifier and the web app user id. I want to be able to pass either to the stored procedure for the where clause.

From my understanding of coalesce if the second field is not null I will not get the records I want.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-03-30 : 09:38:51

where field1 = coalesce(@parm1,@parm2)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-30 : 11:58:35

Here is an example with cf. How would I do this in SQL?

SELECT tablePK
,tblProductsFK
,quantity
FROM #request.sqlObjectPrefix#tblCart
WHERE 1 = 1
<cfif len(attributes.cfUserID)>
AND cfUserID = '#attributes.cf_user_id#'
</cfif>
<cfif len(attributes.personKey)>
AND tblPeopleFK = #attributes.app_user_id#
</cfif>
ORDER BY lastUpdatedOn DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:29:42

quote:
Originally posted by chedderslam

quote:
Originally posted by visakh16

where field1 = coalesce(@parm1,field1)
and field2 = coalesce(@parm2,field2)

What if the second field is not necessarily null.

Basically I have the application server user identifier and the web app user id. I want to be able to pass either to the stored procedure for the where clause.

From my understanding of coalesce if the second field is not null I will not get the records I want.

which second field? so does that mean you need to ignore second parameter filter altogether if first have a value?

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

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-31 : 10:15:28

Basically I want to pass one parameter or another, and use which ever is passed. Right now I have a big if else statement that duplicates the select for either. Very messy.

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 11:36:39

transposing your cf example I come up with:


SELECT tablePK,tblProductsFK,quantity
FROM tblCart
WHERE   (@cfUserID IS NULL OR cfUserID=@cfUserID)
    AND (@tblPeopleFK IS NULL OR tblPeopleFK=@tblPeopleFK)
ORDER BY lastUpdatedOn DESC

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-31 : 12:14:07

Thank you, but I don't believe that will work.

In the db, the two columns may or may not be populated.

So if a record has both cfuserid and tblpeoplefk populated, and I pass in only tblpeoplefk, the non-null cfuserid will exclude the record, which I do not want.

Am I correct?

Am I stuck doing two selects within an if statement?

Here is what I have right now:

IF LEN(ISNULL(@cf_user_id,'')) > 0
BEGIN
DECLARE c1
CURSOR FOR
SELECT tablePK
,tblProductsFK
,quantity
FROM tblCart
WHERE cfUserID = @cf_user_id
ORDER BY lastUpdatedOn DESC

END

IF LEN(ISNULL(@app_user_id,'')) > 0
BEGIN
DECLARE c1
CURSOR FOR
SELECT tablePK
,tblProductsFK
,quantity
FROM tblCart
WHERE tblPeopleFK = @app_user_id
ORDER BY lastUpdatedOn DESC
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:20:39

[code]
Select tablePK,tblProductsFK,quantity from
(
SELECT tablePK,tblProductsFK,quantity,lastUpdatedOn
FROM tblCart
WHERE @cfUserID IS NULL OR cfUserID=@cfUserID
union
SELECT tablePK,tblProductsFK,quantity,lastUpdatedOn
FROM tblCart
WHERE @tblPeopleFK IS NULL OR tblPeopleFK=@tblPeopleFK
) t
ORDER BY lastUpdatedOn DESC
[/code]

Harsh Athalye
http://www.letsgeek.net/

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 12:26:49

quote:

Originally posted by PackRat


SELECT tablePK,tblProductsFK,quantity
FROM tblCart
WHERE   (@cfUserID IS NULL OR cfUserID=@cfUserID)
    AND (@tblPeopleFK IS NULL OR tblPeopleFK=@tblPeopleFK)
ORDER BY lastUpdatedOn DESC

i believe it will work for you, let me break it down a little;


WHERE   (@cfUserID IS NULL OR cfUserID=@cfUserID)
    AND (@tblPeopleFK IS NULL OR tblPeopleFK=@tblPeopleFK)

if @cfUserID is omitted the condition is met by @cfUserID IS NULL.
if @cfUserID is passed it must match cfUserID.
if @tblPeopleFK is omitted the condition is met by @tblPeopleFK IS NULL.
if @tblPeopleFK is passed it must match tblPeopleFK.

(null OR match) AND (null OR match)

if you do not pass a parameter, the param is null, if the param is null it passes the test.

if both params are passed they must both match.
if neither param is passed the whole table is qualified.
if a param is omitted it is not compared and passes the test.

one last version; I promise, using the sql example above, I believe this would be equivalent:


SELECT tablePK,tblProductsFK,quantity
FROM tblCart
WHERE  NULLIF(@cf_user_id, '')=cfUserID 
	OR NULLIF(@app_user_id, '')=tblPeopleFK
ORDER BY lastUpdatedOn DESC

this version would return no matches if neither param were passed or if both params were empty strings.
_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-31 : 12:46:19

ah, you're right. That's great. I was confusing the table value with the db value.

You rock.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:28:35

quote:
Originally posted by chedderslam

ah, you're right. That's great. I was confusing the table value with the db value.

You rock.

And this was same as what webfred suggested long back

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

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 13:42:58

yes; WebFred needed only add the parenthesis to be 100%

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-04-01 : 04:30:59

Ok, next step, same thing for an insert:

INSERT INTO rhkprod_tblcartadjustments
(<cfif len(attributes.personKey)>
tblPeopleFK,
</cfif>
<cfif len(attributes.cfUserID)>
cfUserID,
</cfif>
tblproductsfk,
adjusted,
originalquantity,
adjustedquantity)
VALUES (<cfif len(attributes.personKey)>
@app_user_id,
</cfif>
<cfif len(attributes.cfUserID)>
@cf_user_id,
</cfif>
@tblProductsFK,
1,
@cartQuantity,
@quantity)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:42:42

did nt get that. same as what? what do you want to insert if you dont pass an explicit value?

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

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-04-01 : 08:24:28

I want to insert if either value is passed. This is the same stored procedure, later in the process.

The select gets the records, then if they need to be removed I insert a record into the adjustments table to notify the user.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources