stored procedure with cte and if statement help

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 with cte and if statement help

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-06 : 12:09:49

I have a stored procedure that I need to use an if statement in so that it goes one or or another way depending on what parameter the user passes in. I used a cte to narrow down my data which may or may not be the best way to do this but it was just the way I knew how to get what I wanted but I am running into a problem using it with an if statment in a stored procedure. Here is my code:

create procedure get_emr_users_bysecuritygroup @group_id int as

if @group_id = '-1'

With EMRUser (limit_value) as
(
select distinct limit_value from
(
select * from license_detail
where limit_id = 'EMRUser'
)t
)
select *
from EMRUser t
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where b.group_id like '%EMR%')

else

With EMRUser (limit_value) as
(
select distinct limit_value from
(
select * from license_detail
where limit_id = 'EMRUser'
)t
)
select *
from EMRUser t
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where b.group_id = @group_id

When I try to compile it I get this error:

Msg 319, Level 15, State 1, Procedure get_emr_users_bysecuritygroup, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure get_emr_users_bysecuritygroup, Line 19
Incorrect syntax near ')'.

I have tried to put the semi colon after the if statement line but it still errors out. Does anyone have a better solution?

Thanks.

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-06 : 12:43:33

If I take out the if statement proceeding the declaration of the CTE I can compile it. Well I only included the first cte just to test it out, not the second reference to it. I can't seem to find anything supporting using an if statement before declaring a CTE. If anyone knows anything about this please let me know.

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-06 : 12:47:19

Well in case anyone is following this if I don't use a CTE and just use I think its called a derived query?... I can get it to compile

alter procedure get_emr_users_bysecuritygroup @group_id int as

if @group_id = '-1'
select distinct limit_value from
(select * from license_detail
where limit_id = 'EMRUser')t

select *
from t
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where b.group_id like '%EMR%'

if @group_id <> '-1'

select distinct limit_value from
(select * from license_detail
where limit_id = 'EMRUser')t

select *
from EMRUser t
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where b.group_id = @group_id

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 12:54:25

you can do like this

;With EMRUser (limit_value) as
(
select distinct limit_value from
(
select * from license_detail
where limit_id = 'EMRUser'
)t
)
select * 
from EMRUser t 
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where (b.group_id like '%EMR%' and @group_id = '-1')
or b.group_id = @group_id

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-06 : 12:57:57

Can you run the "guts" of the stored procedure if you DECLARE the GroupID as a variable and execute it?

I was playing around a little bit and I can do IF statements and CTE's without issue. But, I did not try within the context of a stored proc.

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-06 : 12:59:08

That worked great!! Thanks so much. So is it true you cannot use an if statment preceeding a CTE? I assume so because I can't find anythign about it anywhere. I like this solution though and I am sure I will reuse it alot. Thanks again as always you are so helpful! :)

quote:

Originally posted by visakh16

you can do like this

;With EMRUser (limit_value) as
(
select distinct limit_value from
(
select * from license_detail
where limit_id = 'EMRUser'
)t
)
select * 
from EMRUser t 
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where (b.group_id like '%EMR%' and @group_id = '-1')
or b.group_id = @group_id

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-06 : 13:09:59

When I try to just declare the variable with a default to run the cte with the if statment no in the stored procedure I get errors. I may have it set up wrong though.... Here is what I tried

declare group_id int default -1;

if @group_id = '-1'

;With EMRUser (limit_value) as
(
select distinct limit_value from
(
select * from license_detail
where limit_id = 'EMRUser'
)t
)
(select *
from EMRUser t
join user_mstr a on t.limit_value = a.user_id
join user_group_xref b on a.user_id = b.user_id
where b.group_id like '%EMR%')

I get these errors:

Msg 155, Level 15, State 2, Line 1
'int' is not a recognized CURSOR option.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@group_id".
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'select'.

Although Vishka's example with just figuring out the if down in the where clause worked perfectly so maybe its just better to go with that.

quote:
Originally posted by Lamprey

Can you run the "guts" of the stored procedure if you DECLARE the GroupID as a variable and execute it?

I was playing around a little bit and I can do IF statements and CTE's without issue. But, I did not try within the context of a stored proc.

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:12:05

you cant use default with declare statement. you should use like this

declare group_id int

set group_id= -1;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-07 : 04:06:22

quote:
Originally posted by visakh16

you cant use default with declare statement. you should use like this

declare group_id int

set group_id= -1;

You missed @

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 04:30:37

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

you cant use default with declare statement. you should use like this

declare group_id int

set group_id= -1;

You missed @

Madhivanan

Failing to plan is Planning to fail

yeah...just copied it from OP's psoted code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources