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

Question

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?

Oleksandr Fedorenko · Accepted Answer · 2013-03-02 23:35:31Z

10

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

edited Mar 2, 2013 at 23:35

answered Mar 2, 2013 at 23:28

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oleksandr Fedorenko Over a year ago

BTW, for preventing SQL Injection use sp_executesql with parameters

radders Over a year ago

This approach won't work if the OP's function is being used by a Check Constraint - they can't (AFAIK) use stored procedures.

Guffa · Accepted Answer · 2013-03-02 23:35:58Z

2

Functions are limited in what they can use, so that you can use them in a query without accidentally make something that would give horrible performance. Using dynamic queries is one of those things, as that would cause a query planning for each execution, and also would keep the function from being able to be part of a query plan.

You don't need the dynamic query at all in this case, just return the value:

Create Function fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin

return (select Grant_Nr From Grant_Master where grant_id = @paramterValue)

end

answered Mar 2, 2013 at 23:35

Guffa

703k111 gold badges760 silver badges1k bronze badges

Comments

sgeddes · Accepted Answer · 2013-03-02 23:31:09Z

0

I don't think you can use dynamic SQL from a function, nor do I think you need to in your case. Looks like you want something closer to this:

Create Function dbo.fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin
  declare @value int
  declare @SQLString varchar(MAX)

  Select @value=Grant_Nr From Grant_Master where grant_id=@paramterValue

  return @value
end

SQL Fiddle Demo

Also, please check your data types to make sure you're fields are correct. Seems odd to pass in a varchar for the id and return an int for the other field. Either way, this should help you get going in the right direction.

answered Mar 2, 2013 at 23:31

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related