Posted on 2012-08-18
Here is the question, team!
I need to be able to call the EXEC(@MyStatement) in my SCALAR function. This is normally not doable and been told that must use the extended version..
In another word, I will have to be able to construct a "select" string statement and run that in a scalar function. I don't care how that needs to be accomplished but needs to be done this way.
Question: Is this true, and if you have experienced that then would share some thought on that with me.
here is the error:
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.
and the sample code:
declare @Result sql_variant
set @Result = 0
declare @MyKey int
declare @ExecString nvarchar(1000)
set @mykey = (select keytypenum from MyDB.dbo.keytypetable where ltrim(Rtrim(keytype)) = @MyKeyword)
set @execstring = 'select @result = keyvaluecurr from MyDB.dbo.keyitem'+ convert(nvarchar(32), @mykey) +' where itemnum = ' + convert(nvarchar(32), @myinput)
exec sp_executesql @execstring
-- Return the result of the function