Variable for Column name in Scalar-Valued Function

Question

How do I use a variable for Column name in Scalar-Valued Function?

CREATE FUNCTION [dbo].[GetValue]
(
@ID varchar(36),
@PreprtyName varchar(150)
)
RETURNS varchar(250)
AS
BEGIN

DECLARE @RetVal varchar(MAX)
DECLARE @SQL varchar(MAX)
SET @RetVal =''

SET @SQL = 'SET @RetVal = (Select '+ @PreprtyName + ' FROM TableMedia WHERE ID = '''+ @ID +')'''
exec @SQL

SET @RetVal = @RetVal 

RETURN @RetVal
END

Getting error Could not find "Could not find stored procedure"

Here is what I'm trying to avoid.

SELECT     pr.ProductID, tManufacturerImage.Image, tMediaCenter.ManualFileName,tMediaCenter.BrochureFileName, tMediaCenter.AssemblyFileName
FROM         tMediaCenter RIGHT OUTER JOIN
                      tProduct AS pr INNER JOIN
                      tmp_dmi AS dmi ON REPLACE(REPLACE(pr.SKU, 'ACS', ''), 'DMI', '') = RTRIM(LTRIM(dmi.pri_SKU)) ON tMediaCenter.ProductID = pr.ProductID LEFT OUTER JOIN
                      tManufacturer INNER JOIN
                      tManufacturerImage ON tManufacturer.ManufacturerID  =     tManufacturerImage.ManufacturerID ON pr.ManufacturerID = tManufacturer.ManufacturerID 
WHERE     (pr.ManufacturerID = 'f35fc01680-4938-4070-a367-38c31efb01f') AND (dmi.MAP IS     NULL) AND (pr.ParentID <> '')

this does not work for me.

possible duplicate of Executing dynamic SQL in a SQLServer 2005 function — Taryn
– Taryn, Commented May 16, 2012 at 20:35
See the possible duplicate, you are trying to use dynamic sql within a function and it is not allowed. — Taryn
– Taryn, Commented May 16, 2012 at 20:35

Aaron Bertrand · Accepted Answer · 2012-05-16 21:16:41Z

2

You can't. A user-defined function does not support dynamic SQL. You will need to do this with a stored procedure instead, or better define your ultimate goal instead of telling us you need to solve it with dynamic SQL in a function.

CREATE PROCEDURE [dbo].[GetValues]
  @PreprtyName VARCHAR(150)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT ' + @PreprtyName + ' FROM dbo.Table;';

  EXEC sp_executesql @sql;
END
GO

You won't have an easy time doing this inline in your query, sorry. The issue is that there is no way to do this from a function, and there is no way to call a stored procedure for each row in a single query. You could construct a loop and everything else but I think the above procedure fits your requirement without all that extra work and overhead.

If you need to limit it to a certain set of ID values, you'll need to describe how you're trying to do that now.

answered May 16, 2012 at 21:16

Aaron Bertrand

282k37 gold badges468 silver badges468 bronze badges

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

6 Comments

monsey11 Over a year ago

I need to use it in inline queries.

Aaron Bertrand Over a year ago

@monsey11 The fact that you "need to" doesn't make it possible, sorry. If you explain better exactly what you're trying to do, maybe there are workarounds.

monsey11 Over a year ago

for now I'm using "Case" based on the columns in the table. If an additional column is added, I will need to modify the function. and that's what I was trying to avoid.

Aaron Bertrand Over a year ago

How often are you adding columns to this table? You could set up a DDL trigger perhaps that re-creates a view or something. But this seems like an architectural problem.

monsey11 Over a year ago

Added the SQL statement that's not working for me and why i resorted to the function.

|

Collectives™ on Stack Overflow

Variable for Column name in Scalar-Valued Function

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related