Case Statement Select

[jnavarro]

I a SP which I have different where clause however I am unable to find the error. Can you please help me or direction on how I can accomplished the same thing


REATE Procedure AIRetrieval @AssocNum bigint, @Criteria as char(1)
as
Select *
from tblAI
Where Case @Criteria
When 'P' then
AssocNum = @AssocNum and DateResolved is null
When 'C' then
AssocNum = @AssocNum and DateResolved is not null
When 'I' then
DateResolved is null
End

GO

 

[Catadmin]

What is the error you are getting?



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[jnavarro]

This is the error that I am recv

Server: Msg 170, Level 15, State 1, Procedure AIRetrieval, Line 7
Line 7: Incorrect syntax near '='.

 

[Catadmin]

I think you actually need to use the DECLARE keyword to declare your variables.

CREATE Procedure AIRetrieval 
Declare @AssocNum bigint, @Criteria as char(1)
...

You're not setting the value of @AssocNum at all anywhere in your procedure. Are you using it as an input parameter? What about @Criteria? Are you putting two input parameters when you execute your procedure? And are you putting them in the proper order?

I tend to put parens around my THEN statements just for clarity and to make sure that SQL Server knows what I'm telling it to do. I.E.:

...
When 'P' then
        ((AssocNum = @AssocNum) and DateResolved is null)
    When 'C' then
        ((AssocNum = @AssocNum) and DateResolved is not null)
    When 'I' then
        (DateResolved is null)
...

What is the "DateResolved is not null" supposed to mean, BTW? You can't just set a value "Not Null" if that is what you're trying to accomplish. You have to set it equal to some value, even it if is just zero. That is part of your problem, I believe.


Hope this helps.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[jnavarro]

DateResolved is a field in my table which the default value is null.

What I am trying to do is create a where clause statement based on the imput values passed through the parms.

I was able to put () arount my clause statments and that did not work. Any other ideas?

 

[Catadmin]

DateResolved is a field in my table which the default value is null.

Okay, but when you're trying to set it Not Null, what value are you looking to put into it? Like I said, you can't just set it Not Null and expect the little SQL Gnomes inside the server to automatically know what value you expect it to be.

That line is definately close to where your error message says the problem is, so I would resolve that first.

When 'C' then
        ((AssocNum = @AssocNum) and DateResolved is not null)

Should be more like:

When 'C' then
        ((AssocNum = @AssocNum) and DateResolved = '03/21/2005')

Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Pattycake245]

I think it has more to do with the setup of your case statement. Something like this would be syntactically correct.

CREATE Procedure AIRetrieval @AssocNum bigint, @Criteria char(1)

as

Select *
from tblAI
Where AssocNum = case when @Criteria <> 'I' then @AssocNum else AssocNum end
AND isnull(DateResolved,'1900-01-01') = Case when @Criteria <> 'C'
then '1900-01-01' else DateResolved end

Tim

 

[Catadmin]

Wait, a thought just occurred to me. Are you trying to set @AssocNum to equal the AssocNum when your case is 'P' or 'C'? If so, you've got the statement backwards.

Should be
Then @AssocNum = (Select AssocNum from tablename)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[jnavarro]

This is what I am trying to do
I have three SQL statement that I wish to run based on the value passed from the criteria parm

Example
if criteria = 'p'
Select * from tblAI where assocnum=@assocnum and dateResolved is null

If criteria = 'c'
Select * from tblAI where assocnum=@assocnum and dateResolved is not null

if criteria = 'c'
Select * from tblAI where dateAssigned is null

note: DateResolved and Dateassigned the default value is null. My goal is to create one SP and based on the criteria run the approriate SQL statement. Any ideas on how I can do this?

Thank you all for you help

 

[Catadmin]

Actually, you just answered your own question. Sorta. @=)

You'll need to declare either a temp table or use a print statement or declare more variables, but put your select statement in your THEN result. You'll have to play with it, but something like:

CREATE Procedure AIRetrieval @AssocNum bigint, @Criteria as char(1)
as
Declare #TempTable
Select * from tblAI
Where Case @Criteria
    When 'P' then
     Insert into #TempTable  (Select * from tblAI where assocnum=@assocnum and dateResolved is null)
    When 'C' then
      Insert into #TempTable (Select * from tblAI where assocnum=@assocnum and dateResolved is not null)
    When 'I' then
      Insert into #TempTable (Select * from tblAI where  DateResolved is null)
    End

BTW, you don't need the GO command as it actually isn't a T-SQL Command. But, you might consider creating 3 different temp tables if you don't want your values combined with each other. You might also consider making them Global if you need the results outside the SP (Use the ## instead of # in front of the name).




Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[jnavarro]

Thanks
But know I am receiving an error

Incorrect syntax near the keyword 'End'.

I cannot ping point the error

 

[Catadmin]

Post your new code so we can look at it.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[jnavarro]

I remove the insert but I think

this is what it should look like

CREATE Procedure AITest @AssocNum bigint, @Criteria as char(1)
as
Select * from tblAI
Where Case @Criteria
When 'P' then
(Select * from tblAI where assocnum=@assocnum and DateResolved is null)
When 'C' then
(Select * from tblAI where assocnum=@assocnum and DateResolved is not null)
When 'I' then
(Select * from tblAI where DateResolved is null)
Else

End

GO

 

[Pattycake245]

I don't believe you can use case statements in that manner. Besides, doing a select * and then doing another select * is redundant, but I see where you are trying to go.

Did the code I posted earlier not help?

Tim

 

[Catadmin]

You have nothing in your ELSE statement. There is no reason to use ELSE if you're not going to return anything off it. Try taking that off and see if that helps you out.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Pattycake245]

Catadmin, I tried his syntax on one of my tables and recieved the same error. I too was going to suggest to take out the else, or remove the last when statement and put the else there, but even with that out it does not like it when you use selects based on a case condition.

Tim

 

[vongrunt]

This looks like Dynamic SQL Lite (tm)... won't work. If for nothing else, try to remove CASE/WHEN; what remains is syntactical nonsense.

Try this instead:

Select * from tblAI
where 
(	( @Criteria='P' and assocnum=@assocnum and DateResolved is null ) OR
	( @Criteria='C' and assocnum=@assocnum and DateResolved is not null ) OR
	( @Criteria='I' and DateResolved is null )
)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Pattycake245]

I am still curious to know if the code I presented in my first post would do the trick.

 

[Catadmin]

Leave it to vongrunt to come up with the easy solution!

DOH! @=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[vongrunt]

I am still curious to know if the code I presented in my first post would do the trick.

I think it would, assuming that @Criteria is always valid (either P, C or I).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.