stored procedure if - 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

stored procedure if

Author

Topic

craigmac999
Starting Member

14 Posts

Posted - 2007-08-15 : 07:16:32

hi i have a stored procedure which i want to add a if statement to thw where clasue

where Code = 'ghht'
if @category <> '%' and catid = @category
ORDER BY Code

but i am not sure how to do the if statement in sql?

pootle_flump

1064 Posts

Posted - 2007-08-15 : 07:30:37

Hi

That does not make sense I am afraid. Since this is a table there will be lots of different values for catid. As such the statement does not simply evaluate to true or false.

craigmac999
Starting Member

14 Posts

Posted - 2007-08-15 : 07:50:11

ok i have this select state below

and if the parameter @category = %
then i need to
select all where Code = 'ddgg'
else
select rows that St_Cat_1 = @category

how can i do this>?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-15 : 08:01:47

http://www.timfanelli.com/images/no_idea.jpg

where
(@category = '%' and code = 'ddgg')
OR
(@category <> '%' and st_cat_1 = @category)

Jim

craigmac999
Starting Member

14 Posts

Posted - 2007-08-15 : 09:14:49

ok thanks i now have the code below which works if a "1" is not supplied, but when a "1" is supplied it is showing no results, i need it to show all categories

FROM dbo.STOCK
WHERE Code = 'AE' AND Level <> 0
AND
(@category <> '1' and st_cat_1 = @category)
ORDER BY Code

so if the @category = 1 only have WHERE Code = 'AE' AND Level <> 0

if the @category <> 1 have WHERE Code = 'AE' AND Level <> 0 and st_cat_1 = @category

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-15 : 09:25:42

[code]WHERE Code = 'AE'
AND [Level] <> 0
AND (
@category = 1
OR st_cat_1 = @category
)[/code]

craigmac999
Starting Member

14 Posts

Posted - 2007-08-16 : 03:07:08

ok i am getting an error with that

what i need is

IF @category has a value of 1 then

WHERE Code = 'AE' AND STS.St_Level = 0

ELSE

WHERE Code = 'AE' AND STS.St_Level = 0 AND st_cat_1 = @category

ORDER BY STS.St_Code

i dont think AND (@category = 1 OR st_cat_1 = @category) is working correctly?

pootle_flump

1064 Posts

Posted - 2007-08-16 : 06:10:41

What's your error?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-16 : 06:27:58

WHERE
(Code = 'AE' AND STS.St_Level = 0 AND @category = 1)
OR
(Code = 'AE' AND STS.St_Level = 0 AND st_cat_1 = @category AND @category <> 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources