Stored Procedure If Statement - 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 2005 Forums

Transact-SQL (2005)

Stored Procedure If Statement

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 11:02:17

Hello,

I am new to this.

I am creating am trying to create a stored procedure that looks to see what group_id number a user selected and if they select no group_id number than it should return one of a specific set that I specify in my procedure, but if they pick just one group_id number I only want it to return those records. Here is my procedure. When I run in Managment Studio it says I have errors near my if, then and else.

Any Ideas?? Thanks in advance!

CREATE PROCEDURE EMRUserSecurityGroups @group_id int

If @group_id = "*All" then
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32')
else
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id = @group_id

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 11:15:29

I solved my if problem by taking out the then. I also switched it to look for a null instead of *ALL. But how do I execute the stored procedure to test and it pass in null??

exec EMRUserSecurityGroups (null) does not work

Here is my code I am using in procedure:

CREATE PROCEDURE EMRUserSecurityGroups @group_id int As

If @group_id is null
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32')
else
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id = @group_id

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 11:19:01

Nevermind I solved it I just wasn't supposed to have my parameters in paranthesis. Also I can't pass null to an int field so I had to switch it to -1. Thanks anyways. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 11:20:14

quote:
Originally posted by sross81

I solved my if problem by taking out the then. I also switched it to look for a null instead of *ALL. But how do I execute the stored procedure to test and it pass in null??

exec EMRUserSecurityGroups (null) does not work

Here is my code I am using in procedure:

CREATE PROCEDURE EMRUserSecurityGroups @group_id int As

If @group_id is null
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32')
else
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id = @group_id

You need only this:-

CREATE PROCEDURE EMRUserSecurityGroups 
@group_id int 
As
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE (b.group_id = @group_id 
OR b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32'))
GO

AND invoke like this

EXEC EMRUserSecurityGroups YourIDValue
OR
EXEC EMRUserSecurityGroups NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources