March 6, 2008 at 3:45 pm
Is it possible to call a function from sp_executesql? I need to specify the function name with a variable and in addition, I need to trap the return value from the fn in the @FnValue variable. I am getting a syntax error on my function name when the EXEC line executes.
DECLARE @strExec nvarchar(200)
DECLARE @params nvarchar(200)
SET @strExec = @FunctionName + ' @InPtHt, @HtUnits'
SET @params = '@InPtHt dec(9,3), @InHtUnits varchar(10)'
EXEC @FnValue = sp_executesql
@strExec,
@InPtHt = @PtHt,
@InHtUnits = @HtUnits
March 7, 2008 at 3:01 am
usually you put 'select' before function name.
SET @strExec = 'select ' + @FunctionName + ' @InPtHt, @HtUnits'
if this is a scalar function, declare output parameter and pass it to sp_executesql
Piotr
...and your only reply is slàinte mhath
March 7, 2008 at 8:26 am
I got it to work using:
SET @strExec = N'exec @ValueOUT=@CustomFunctionName @InPtHt, @InHtUnits'
SET @params = N'@CustomFunctionName nvarchar(200),@InPtHt dec(9,3),@InHtUnits varchar(10), @ValueOUT dec(9,3) OUTPUT'
EXEC sp_executesql @strExec,
@CustomFunctionName = @CustomFunctionNametxt,
@InPtHt = @PtHt,
@InHtUnits = @HtUnits,
@ValueOUT=@Value OUTPUT;
select @Value
BUT... does having an 'exec' embedded in my @strExec string defeat the purpose of using sp_executesql? I am not worried about sql injection in this case so should I just do this instead?
exec @CrClValue = @CustomFunctionNametxt @InPtHt, @InHtUnits
select @CrClValue
March 7, 2008 at 8:33 am
BUT... does having an 'exec' embedded in my @strExec string defeat the purpose of using sp_executesql? I am not worried about sql injection in this case so should I just do this instead?
I would say to keep it the way you have it. You query is parameterized via sp_executesql. Prameterization leads to query plan reuse and faster results.
If you choose to take this route
exec @CrClValue = @CustomFunctionNametxt @InPtHt, @InHtUnits
select @CrClValue
There is no query plan reuse and the statement is compiled each time.
March 7, 2008 at 11:06 am
Adam, could you explain why there is no plan reuse in the latter case?
Piotr
...and your only reply is slàinte mhath
March 7, 2008 at 11:17 am
If you use the exec method, SQL server cannot reuse query plans. This means that everytime the code is run SQL server must generate a new query plan. The alternative sp_executesql, which allows SQL to use the existing query plan. This can increase performance because SQL already has most of what it needs to generate an optimal plan and fills in the gaps, with the stuff it does not know.
Basically, the benefit is SQL does not have to generate a new query plan everytime the code is run.
March 7, 2008 at 11:23 am
Duh, of course, exec. My mind is focused on weekend now 🙂
Thanks!
On the other hand, Stef is also using exec:
N'exec @ValueOUT=@CustomFunctionName @InPtHt, @InHtUnits'
wouldn't it be better to use set/select?
N'set @ValueOUT=@CustomFunctionName @InPtHt, @InHtUnits'
Piotr
...and your only reply is slàinte mhath
March 7, 2008 at 11:23 am
For example, say you use sp_executesql and you have 2 parameters name and city. If you execute this statement
set @sql = 'select * from my table where name = @name and city=@city'
exec sp_executesql @sql, @parameters, @city = mycityvar, @name = mynamevar
When this statement is executed a query plan is created capturing all the elements to make this query run optimally. It holds all of this information in a query plan. The next time you compile this query, SQL will not have to change the plan at all because it can reuse the same plan as before; however, if the variables change then SQL can reuse the plan, but will reoptimize the plan for the current variables.
This process alleviates SQL from have to build a plan from scratch each time.
March 7, 2008 at 11:25 am
Duh, of course, exec. My mind is focused on weekend now
Mine too 😀
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply