stored procedure parameter - 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 2000 Forums

SQL Server Development (2000)

stored procedure parameter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-06 : 08:00:26

Clifford Fortuna writes "In my stored procedure which accepts PARAMETERS. In my SELECT statement the where clause sometimes has a value and sometimes doesn't have. Does anybody know how to do it in SP.

FIRST SCENARIO
@custID = '0002'

Select * from Customers
where last_name = 'Fernandez'
and cust_id = @custID

SECOND SCENARIO
@custID = '' --->> equal to null

Select * from Customers
where last_name = 'Fernandez'
and cust_id = @custID

Please help..."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-06 : 08:16:05

Select * from Customers
where last_name = 'Fernandez'
and
case when cust_id is null then true else cust_id = @custID end

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 09:01:10

How about


SELECT *  
  FROM Customers
 WHERE last_name = 'Fernandez'
   AND cust_id = ISNULL(@custID,cust_id)

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-06 : 09:31:31

where
@custID is null OR (CustID = @CustID)

Vit -- "true" has no meaning in SQL Server -- you can't return boolean values.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-07 : 02:14:23

It was the Access hanky-panky... :)

- Vit

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-07 : 05:08:53

Try this One, This would work perfectly.

Select * from Customers
where last_name = 'Fernandez'
and cust_id = coalesce(@custID,cust_id )

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources