Home » SQL ServerRSS

Using EXECUTE on dynamic SQL string containing scalar valued function with parameters

I would like to use EXECUTE for calling a scalar valued function as dynamic SQL and for assigning the return value to a variable. This is working fine as long as the scalar valued function does not have parameters. E.g.:

DECLARE

 

@Result bit, @Function nvarchar(MAX)
SET @Function ='dbo.CheckLimit'
EXECUTE @Result = @Function

However when trying the same with a scalar valued function that requires parameters I am running into problems:

DECLARE

 

@Result bit, @Function nvarchar(MAX)
SET @Function ='dbo.CheckLimit ''SELECT 1'', 1, 1'
EXECUTE @Result = @Function

SQL Server thinks dbo.CheckLimit should be a stored procedure, but a stored procedure with that name does not exist:

Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'dbo.CheckLimit 'SELECT 1', 1, 1'.

Can anybody tell if there is a way to call a scalar valued function in that way? I intend to use this inside a table valued function, so calling a stored procedure instead is no option.

My customer is currently using SQL Server 2005.

Anticipative thanks and kind regards
Sebastian Daser
Softfount IT Solutions

 

8 Answers Found

 

Answer 1

I am not sure that understand what purpose of dynamic SQL here

Perhaps you need SELECT dbo.CheckLimit 'SELECT 1', 1, 1 AS col?????

 

Answer 2

 

declare @Result bitselect @Result = dbo.CheckLimit('SELECT 1', 1, 1)

 

 

 


Best regards
 

Answer 3

Since you are calling a function and not a stored procedure you should be doing something like...

select * from dbo.CheckLimit ('SELECT 1'', 1, 1)

 

 

Thanks

 

Answer 4

DECLARE @i INTDECLARE @param NVARCHAR(20) = '@o INT OUTPUT'DECLARE @sql NVARCHAR(200) = 'SELECT @o = dbo.CheckLimit(''SELECT 1'', 1, 1)'EXEC sp_executesql @sql, @param, @o = @i OUTPUTSELECT @i
 

Answer 5

@Result bit,@Function nvarchar(MAX)
SET @Function='dbo.CheckLimit ''SELECT 1'', 1, 1' EXECUTE @Result= @Function

You need to pass the parameters in the EXEC statement:

EXECUTE @Result= @Function 'SELECT 1', 1, 1

(Although it looks funny with a string like 'SELECT 1' being passed to a UDF.)

Note that "EXECUTE @function" is not dynamic SQL; it's just a matter of indirection.

Can anybody tell if there is a way to call a scalar valued function in that way? I intend to use this inside a table valued function, so calling a stored procedure instead is no option.


And if you really want to use dynamic SQL, that is the parameter list has to  part of the query string, it's time for a redesign. You cannot use dynamic SQL in functions.

 

Answer 6

Hi Erland,

What I actually intend to do is call one of multiple scalar valued function from within a table valued function where the name of the scalar valued function is configured in a table field.

Since all of the scalar valued functions have the same parameters your solution works for me great.

Many Thanks
Sebastian Daser
Softfount IT Solutions

 

Answer 7

DECLARE @i INTDECLARE @param NVARCHAR(20) = '@o INT OUTPUT'DECLARE @sql NVARCHAR(200) = 'SELECT @o = dbo.CheckLimit(''SELECT 1'', 1, 1)'EXEC sp_executesql @sql, @param, @o = @i OUTPUTSELECT @i


every day is a school day


I cannot use sp_executesql since the call is in a table valued function used in a query. The correct syntax, as pointed out by Erland Sommarskog is:

EXECUTE

 

@Result = @Function 'SELECT 1', 1, 1

Thanks anyway
Sebastian

 

Answer 8

I am not sure that understand what purpose of dynamic SQL here

Perhaps you need SELECT dbo.CheckLimit 'SELECT 1', 1, 1 AS col?????


Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

That does not work for me since the function name comes in a variable. The correct syntax, as pointed out by Erland Sommarskog is:

EXECUTE

 

@Result = @Function 'SELECT 1', 1, 1

Thanks anyway
Sebastian

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter