Stored procedure - test param value

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

Transact-SQL (2000)

Stored procedure - test param value

Author

Topic

VinyGrr
Starting Member

3 Posts

Posted - 2004-12-01 : 06:09:53

Hello ,

I'm newbie to stored procedure
I'm trying to do something like this but i think there's a problem with my if condition...somebody can help me? thanks in advance

CREATE PROCEDURE viewGarage
@proc_IdGarage as BIGINT
AS
SELECT IdGarage,gName
FROM tblGarage
IF @proc_IdGarage <> 0
WHERE IdGarage = @proc_IdGarage
END
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-01 : 06:29:41

quote:

CREATE PROCEDURE viewGarage
@proc_IdGarage as BIGINT
AS
SELECT IdGarage,gName
FROM tblGarage
IF @proc_IdGarage <> 0
WHERE IdGarage = @proc_IdGarage
END

you can't combine if with a select statement, use case if needed.

SELECT IdGarage,gName
FROM tblGarage
WHERE IdGarage = @proc_IdGarage and @proc_IdGarage<>0

--------------------
keeping it simple...

VinyGrr
Starting Member

3 Posts

Posted - 2004-12-01 : 08:31:07

Thanks but this didn't solve my problem...in fact i need to add the where clause to my query, only when the param neq 0 so i tries with case like this but still error? Any ideas for me? thanks for your help!

CREATE PROCEDURE viewGarage
@proc_IdGarage as BIGINT
AS
SELECT IdGarage,gName
FROM tblGarage
CASE @proc_IdGarage <> 0
WHEN 1
WHERE IdGarage = @proc_IdGarage
END
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 09:08:22

you probably want this:
select IdGarage,gName
from tblGarage
where (@proc_IdGarage = 0) or IdGarage = @proc_IdGarage

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-02 : 00:42:37

CREATE PROCEDURE viewGarage
@proc_IdGarage as BIGINT
AS

if @proc_idgarage=0
SELECT IdGarage,gName
FROM tblGarage
where idGarage = @proc_IdGarage
else
select idgarage,gname,from tblgarage

GO

--------------------
keeping it simple...

VinyGrr
Starting Member

3 Posts

Posted - 2004-12-02 : 07:30:19

great...exactly what i need ! many thanks for your help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-02 : 07:40:17

you do understand that my statement does the same thing without the if ... else construct, do you?
if @proc_IdGarage = 0 then show all records else just those that have the id, right?

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-05 : 22:07:34

not sure but doesn't it show for both conditions since the operator is 'or'?

i think he needs only to have the criteria if the @proc_IdGarage=0 otherwise no criteria.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources