Function returning wrong result in dynamic query

Question

I need some help. I am working with a SQL function that generates a dynamic query and apparently everything is correct, it does not throw any error, the problem is that when I call the function the result I get is the complete query and I need the result of the query. I don't know how to proceed because I can't use instructions like EXEC sp_executesql @sql inside a function.

Any advice?

Here's my function

CREATE FUNCTION [dbo].[GetAmount](
    @Table NVARCHAR(30),
    @Column NVARCHAR(30),
    @Id NVARCHAR(30)
)
RETURNS NVARCHAR(MAX) 
AS 
BEGIN
RETURN ('SELECT ' + @Column + ' FROM ' + @Table + ' WHERE ID = ' + @Id)
END

call to function

dbo.GetAmount('Sales','Amount','123')

this is the result I got

SELECT Amount FROM Sales WHERE ID = 123

result I want (amount of sales)

$230

I know that there are simpler ways to do this but due to the structure of the database it is necessary to apply this solution, also the same function will be used to retrieve values from different tables.

You need to execute the select query I assume as it is just like a text field which you created in your function. — Avi, Aug 18 '20 at 20:51
I tried but I am getting an error I think that EXEC cannot be used inside a function — asdf31, Aug 18 '20 at 20:52
There are strict rules about functions. One of those is that they cannot execute dynamic sql. And since your function returns a scalar (string) value, you should not expect anything but a string containing the concatenated values. You have taken the wrong path - start over. — SMor, Aug 18 '20 at 20:53
Call the function, get the query, and then execute it in the calling code to get the value. If you're trying to use the function in another query, well, you're kind of out of luck on that one. — pmbAustin, Aug 18 '20 at 21:14
In terms of security, it is a bad direction to be building query from input strings, like SQL Injection. For speed, functions and stored procedures need to be compiled at create time to use specific objects with a specific plan. And if table content changes drastically, they need to be recompiled. — David G. Pickett, Aug 18 '20 at 21:14
Also, if you're passing all that information in, then you already know it at the point you're calling the function, so just hard-code the SELECT in there at that point. — pmbAustin, Aug 18 '20 at 21:26
Sorry for the late reply, thank you all for your support. @SMor was right, it was impossible to get my code to work with a function but I changed my approach and used a stored procedure and solved the issue — asdf31, Aug 21 '20 at 20:43

score 0 · Answer 1 · answered Aug 18 '20 at 22:34

Like this:

CREATE OR ALTER FUNCTION [dbo].[GetAmount](
    @Table NVARCHAR(30),
    @Column NVARCHAR(30),
    @Id bigint
)
RETURNS NVARCHAR(MAX) 
AS 
BEGIN
    RETURN ('SELECT ' + quotename(@Column) + ' FROM ' + quotename(@Table) + ' WHERE ID = ' + cast(@Id as nvarchar(20)))
END

go

declare @sql nvarchar(max) = dbo.GetAmount('Sales','Amount','123')
exec (@sql)

Function returning wrong result in dynamic query

1 Answers1