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](http://stackoverflow.com/questions/150552/executing-dynamic-sql-in-a-sqlserver-2005-function) — Taryn, May 16 '12 at 20:35
See the possible duplicate, you are trying to use dynamic sql within a function and it is not allowed. — Taryn, May 16 '12 at 20:35

score 2 · Answer 1 · answered May 16 '12 at 21:16

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 '12 at 21:16

Aaron Bertrand

272,866
37
466
490

I need to use it in inline queries. – monsey11 May 17 '12 at 14:53
@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. – Aaron Bertrand May 17 '12 at 14:54
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. – monsey11 May 17 '12 at 14:56
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. – Aaron Bertrand May 17 '12 at 15:03
Added the SQL statement that's not working for me and why i resorted to the function. – monsey11 May 17 '12 at 15:35
@monsey11 I would suggest creating a new question (this one is almost certainly going to be closed as a duplicate) and explain what you are trying to accomplish instead of how you want to accomplish it. Maybe a simpler example and what the end result of your inline query should be. There may be another workaround but I'm having a real hard time relating the query you added to the question (which doesn't involve TableMedia at all) to the function you were trying to write. – Aaron Bertrand May 17 '12 at 19:42

Variable for Column name in Scalar-Valued Function

1 Answers1