Conditional statements inside Functions/StoredProc

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)

Conditional statements inside Functions/StoredProc

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 04:10:06

Hello All
My task is to have a conditional statement inside a stored procedure.
The results returned should depend on the parameters i send.
However Im not able to execute these stored procedures or functions
because of the syntax errors

Inside stored procedure(say usp_MyProc) i have something like this...


If @flag = 0
    SELECT Col1 from X          //@flag(bit) is an input parameter
Else   
    SELECT Col1 from Y

I get an error when i use the procedure inside a case statement

Select SomeColA,
SomeColB =
CASE [tablenumber]
WHEN 1 THEN usp_MyProc 1
WHEN 0 THEN usp_MyProc 0
END
FROM SomeTable

Could anyone tell me how to execute stored procs or functions in such a scenario ?

Regards
Srivatsa

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 04:19:01

??? how can you select 'usp_proc' inside 'some table'?
or maybe im wrong...please guide

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 04:23:03

quote:
I get an error when i use the procedure inside a case statement

Select SomeColA,
SomeColB =
CASE [tablenumber]
WHEN 1 THEN usp_MyProc 1
WHEN 0 THEN usp_MyProc 0
END
FROM SomeTable

You can execute stored procedure from a SELECT query. You will need to perform some sort of loop (while or cursor) and execute the stored procedure within the loop.

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 04:40:26

Hello
Actually the following query works if i use functions instead of stored procedures. However if i use stored procedure i am not able to
execute it.
I just wanted to know whether we can use the stored procedures in this way.

Regards
Srivatsa

quote:
Originally posted by khtan

quote:
I get an error when i use the procedure inside a case statement

Select SomeColA,
SomeColB =
CASE [tablenumber]
WHEN 1 THEN usp_MyProc 1
WHEN 0 THEN usp_MyProc 0
END
FROM SomeTable

You can execute stored procedure from a SELECT query. You will need to perform some sort of loop (while or cursor) and execute the stored procedure within the loop.

KH
[spoiler]Time is always against us[/spoiler]

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-07 : 04:44:23

no you can't use sp this way
you select functions but execute sp

in our current case it would be best to stick to functions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources