Getting an error when executing a dynamic sql within a function (SQL Server)?

[Originally posted by]: http://stackoverflow.com/questions/15180561/getting-an-error-when-executing-a-dynamic-sql-within-a-function-sql-server

I create a function to execute dynamic SQL and return a value. I am getting “Only functions and some extended stored procedures can be executed from within a function.” as an error.

The function:

Create Function fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin
declare @value nvarchar(500);

Set @SQLString  = 'Select Grant_Nr From Grant_Master where grant_id=' + @paramterValue

exec   sp_executesql
       @query = @SQLString,       
       @value = @value output

return @value   
end 

The execution:

Select dbo.fn_GetPrePopValue('10002618') from Questions Where QuestionID=114

and:

Select fn_GetPrePopValue('10002618') from Questions Where QuestionID=114

Is the function being called properly or is the function incorrect?

shareedit

 

You cannot use dynamic SQL from a function, neither can you call stored procedures.

Create proc GetPrePopValue(@paramterValue nvarchar(100))
as
begin
declare @value nvarchar(500),
        @SQLString nvarchar(4000)

Set @SQLString = 'Select @value = Grant_Nr From Grant_Master where grant_id = @paramterValue'

exec sp_executesql @SQLString, N'@paramterValue nvarchar(100)', 
       @paramterValue, 
       @value = @value output

return @value   
end 
shareedit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s