June 27, 2008 at 1:07 am
How can i conditionally call a stored procedure in SQLSERVER using IF/CASE statement. The Value passed to Procedure changes depending on the condition.
Thanks in Advance...
Gurumoorthy
June 27, 2008 at 1:34 am
June 27, 2008 at 1:37 am
If you want to execute stored procedure conditionally then you can do like this:
Declare @Val1 int,@Val2 int,@Val3 int,@Val4 int
SELECT @Val1=1,@Val2=2,@Val3=3,@Val4=4
IF (Condition)
EXEC ProcName @Val1,@Val2
Else
EXEC ProcName @Val3,@Val4
June 27, 2008 at 1:41 am
If you want to store the result in a table then table must be created prior to execute the procedue
like:
Create Table #T(ID int, Name varchar(100), DOB DateTime)
Declare @Val1 int,@Val2 int,@Val3 int,@Val4 int
SELECT @Val1=1,@Val2=2,@Val3=3,@Val4=4
IF (Condition)
INSERT INTO #T EXEC ProcName @Val1,@Val2
Else
INSERT INTO #T EXEC ProcName @Val3,@Val4
Now your procedure must return three columns otherwise you can mention the name of columns if procedure returns less then three.
like:
IF (Condition)
INSERT INTO #T(Columns) EXEC ProcName @Val1,@Val2
Else
INSERT INTO #T (Columns) EXEC ProcName @Val3,@Val4
June 27, 2008 at 3:01 am
Hi Hari,
Thanks for your suggestions.
Sorry for not mentioning the scenario clearly..
The Situation is this...
I should derive a single select query that would return 8 columns. 3 column values are directly fetched from the tables.The rest 5 column's values are determined by executing a sp passed with the value of particular field in the table.
the sample query is here..
Select
S.Serverid,
SC.ConfigID,
S.StatusID,
IF R.ResourcetypeId=1
--Call the SP that is passed with the values from table and this resourcetypeid and return a description that is assigned for this column,
If R.ResourceTypeID=2
--Call the SP that is passed with the values from table and this resourcetypeid and return a description that is assigned for this columns,
... similarly for 3 other columns.
FROM
relevant tables
Here I couldn't use the Procedure call within If and throws error.
please help me on this and let me know if my way of approach is correct.
Thanks
Gurumoorthy
June 27, 2008 at 3:20 am
Hi Gurumoorthy,
You can't execute procedure in this way.
It could be better if you have had Table Valued Function instead of Stored Procedure. Because you can't use SP in SELECT, WHERE or ORDER By clause.
June 27, 2008 at 3:28 am
Hi Hari,
Initially I tried for Functions. Since I need to use a dynamic query in that I can't use the function.
Is there any other approach that can be followed to achieve this?
Thanks
June 27, 2008 at 3:50 am
Gurumoorthy,
we can use Functions in dynamic queries....
there is no problem using functions in Dynamic queries
June 27, 2008 at 4:33 am
Hari,
I tried using drynamic query with in the function but getting the following error while executing.
'Only Functions and extended stored procedures can be executed from within a function.'
Thanks
June 27, 2008 at 4:51 am
Gurumoorthy,
just give me your complete code with few sample data.
I'll come up with some solution.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply