WHERE CASE? - 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

General SQL Server Forums

New to SQL Server Programming

WHERE CASE?

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 13:59:00

I want to pass four variables to a stored procedure all which default to NULL

Example:
CREATE PROCEDURE spSOMETHING
(@varA nvarchar(50)=NULL,
@varB nvarchar(50)=NULL,
@varC nvarchar(50)=NULL,
@varD nvarchar(50)=NULL)

AS
SET NOCOUNT ON

-- Pseudocode:
--IF varA IS NOT NULL
-- SELECT * FROM table WHERE varA = @varA
-- elseif varB IS NOT NULL
-- SELECT * FROM table where varB = @varB
-- elseif varC IS NOT NULL
-- SELECT * FROM table where varC = @varC
-- else
-- SELECT * FROM table where varD = @varD

What I've done is create 4 stored procedures and I'll call each one going through a daisychain of IF...ELSE statements (there is no actual ELSEIF in the code, as I don't know if this is valid in T-SQL). I'd like to just have the code from the other 4 stored procedurs in this one, conditionally selecting WHERE clauses. I'd even like to be able to select multiple if possible (i.e. WHERE varA=@varA AND varD=@varD if varA and varD are not null and varB and varC are not passsed).

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 14:01:33

Are you just trying to implement a dynamic WHERE clause. If so, here's the efficient way to do it:
http://www.sqlteam.com/item.asp?ItemID=2077

And here's some good reading on dynamic ORDER BY (make sure to check out the comments about the different data types):
http://www.sqlteam.com/item.asp?ItemID=2209

Tara Kizer
aka tduggan

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 15:56:57

Tara,

Thanks for the reply. The article doesn't quite cover my case (If I read it correctly). From what I could tell, the methods illustrated allow you to select the condition to compare the fields against, not to select the fields themselves. In this case, I need to select from one or more columns in a table. I'm working with an existing table architecture that is basically a spreadsheet acting as a database table, which makes it sort of difficult for me.

Perhaps it will help to show the schema of the table I'm pulling from as it pertains to the example I supplied.

I also missed the @ sign in front of my pseudocode variables, so please allow me to rephrase the question:

Example table:

sampletable
fieldA nvarchar(50)
fieldB nvarchar(50)
fieldC nvarchar(50)
fieldD nvarchar(50)
fieldE nvarchar(50)
fieldF int

Exampe stored procedure:

CREATE spExampleStoredProc
(@var_a nvarchar(50) = NULL,
@var_b nvarchar(50) = NULL,
@var_c nvarchar(50) = NULL,
@var_d nvarchar(50) = NULL)
AS
SET NOCOUNT ON

IF @var_a IS NOT NULL
BEGIN
SELECT fieldE,fieldF FROM sampletable WHERE fieldA = @var_a
END
ELSE
BEGIN
IF @var_b IS NOT NULL
BEGIN
SELECT fieldE,fieldF FROM sampletable WHERE fieldB =@var_b
END
ELSE
BEGIN
IF @var_c IS NOT NULL
BEGIN
SELECT fieldE,fieldF FROM sampletable WHERE fieldC =@var_c
END
ELSE
BEGIN
SELECT fieldE,fieldF FROM sampletable WHERE fieldD =@var_d
END
END
END
GO

This works for me for now, but gets kind of repetetive and sloppy. I also am limited with the above to where I have to prioritize, so if @var_A is not null, then it doesn't matter what the other three are. I'd like to be able to say "if var_a and var_c are passed, lets get fields E and F where fieldA=var_a and fieldC=var_c".

I have to say, I really appreciate everyone on this forum. You've helped me tremendously with my learning curve on this stuff. The suggestion that was offered for this particular challenge, while not pertaining to this exactly will surely help me in the future.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 16:01:44

That article can do what you are asking. It allows you to put all of your different options in one single query. The trick is to understand what COALESCE is doing. If I'm still way off, then please provide a data example so that we can better help you.

Tara Kizer
aka tduggan

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 17:10:39

Thanks Tara, I think a light bulb just went on. I apologize for not looking more closely at the article, as I was scanning it over and missed the overall point. It works! Thanks for your patience.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources