dynamic stored procedure -- sql server (microsoft)

alex
6/28/2005 8:17:54 PM

i would like to execute the following logic in a stored procedure:

if (<condition>)
return 1
else
return 0

The catch is that the condition "string" is coming as input to the stored
proc. I.e. the condition is not hardcoded. Further the condition itself is
going to need to call other stored procs. E.g:

if (AverageSP() > 10)
return 1
else
return 0

Where, again, the "AverageSP() > 10" condition is fed to the stored proc at
run time.

Any clues on how to approach this?

Alex.

alex
6/28/2005 9:58:40 PM

Well, I found a solution of sorts that seems to be working. The only change
I had to do was that the condition uses User Defined functions vs other
stored procs....

CREATE PROCEDURE TestDynamic AS

declare @condition nvarchar(100)
declare @query nvarchar(100)
declare @returnValue bit

set @condition='mydb.AvgValue(1) > 10'
set @query='if (' + @condition + ') select @returnValue=1 else select
@returnValue=0'
exec sp_executesql @query, N'@returnValue bit out', @returnValue out
print cast(@returnValue as char(5))
GO

[quoted text, click to view]

sql server (microsoft) : dynamic stored procedure