Dynamically Add Column Name

MadDashCoder

Hi all, I have a function that receives a column name and it will return this column name when the function is called, if the column name is not null. This function will be called in a stored proc inside of the Select statement. The function will return the name of a column entered as input and it will be used inside of the Select statement. The following is my function

Create function GetName(@C nvarchar(50))
Returns nvarchar(50)
As
Begin
Declare @Col_Name nvarchar(50)
IF (@Col IS NOT null)
Begin
Set @ColumName = @Col
END
Return @Col_Name
End

I expect this function to return any input a user enters but it doesn't return anything and I'm not getting any errors. Also how do I call this function in my stored proc so that the column name is added to the select statement automatically. Any help is great, thanks.

Sascha Lefevre

You can't call that function in a select-statement in order to modify the column-list of the select-statement. You need to build your query string dynamically. See here: https://msdn.microsoft.com/en-us/library/ms188001.aspx[^] http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Corporal Agarn

You made a typo in your explanation code, at least I hope it is not in your actual function. @Col_Name is not defined. @ColumName is.

Mongo: Mongo only pawn... in game of life.

MadDashCoder

Hi, thank you all for responding. I decided to use a stored proc instead of a function and used Dynamic SQL to write my query.

Create Porocedure GetName
(
@column nvarchar(50)
@columnData nvarchar(50)
)
As
Begin
Declare @MyQuery nvarchar(300)

set @column= IF (@column IS NOT null AND @column ='TableA.SomeColumn')
Begin
Set @MyQuery = N'Select'+ TableA.[@column] + 'from dbo.TableA
(where TableA.Column1 LIKE' + @columnData + '+ ''%'' OR' + @columnData + 'IS null)'
END
End
EXEC sp_executesql;

I get no errors but it does not work. Any help will be greatly appreciated, thanks.

Corporal Agarn

The statement "EXEC sp_executesql;" should include your variable (@MyQuery)

Mongo: Mongo only pawn... in game of life.

Richard Deeming

Try this:

CREATE PROC dbo.GetName
(
@column sysname,
@columnData nvarchar(50)
)
As
BEGIN
DECLARE @MyQuery nvarchar(max), @RealColumnName nvarchar(130);

SET NOCOUNT ON;

-- Verify that the column name exists in the table:
SELECT
    @RealColumnName = QuoteName(name)
FROM
    sys.columns
WHERE
    object\_id = OBJECT\_ID('dbo.TableA')
And
    name = @column
;

If @RealColumnName Is Null
BEGIN
    RAISERROR('Invalid column name: "%s"', 16, 1, @column);
    Return;
END;

-- Use sp\_executesql to pass the parameter to the dynamic query as a parameter:
-- https://msdn.microsoft.com/en-gb/library/ms188001.aspx
SET @MyQuery = N'SELECT ' + @RealColumnName + N' FROM dbo.TableA WHERE (' + @RealColumnName + N' Like @columnData + N''%'' Or @columnData Is Null)';
EXEC sp\_executesql @MyQuery, N'@columnData nvarchar(50)', @columnData = @columnData;

END

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

MadDashCoder

Hi, thanks for replying. I caught that error and my query now runs but it returns nothing

Corporal Agarn

Now that is a good explination

Mongo: Mongo only pawn... in game of life.