Retrieve parameter value from parameter name as a string in SQL

Question

I am trying to use some dynamic sql. I have generated over 100 parameters, and to speed things up I am trying to use dynamic sql to only insert values into a table that I have to based off of information retrieved from other tables. I have tried many things like adding a cast etc.

Example of what I mean:

DECLARE @var1 NVARCHAR(MAX)

-- Loop through and add various values
SET @var1 = @var1 + @parameterName
-- The parameter name is retrieved from a table that holds this information

the problem is that when I add the parameter name which would be like "@myFirstParameter" into my final expression so something like this:

DECLARE @finalString NVARCHAR(MAX)
SET @finalString = 'INSERT INTO myTableName ([myFirstParameter]) VALUES (@myFirstParameter)'
EXEC(@finalString)

The "@myFirstParameter" does not get replaced by it's value and I get the following error: Must declare the scalar variable "@myFirstParameter".

Does anyone know of a way to go from the string name of a parameter to the actual value? I am trying to avoid hardcoding all the values and any work around I have attempted has failed and given me errors which appear to be much worse than what I have stated above.

Andomar · Accepted Answer · 2012-07-25 20:22:37Z

1

The first way is to add the parameter's value, instead of its name, to the SQL string:

SET @finalString = 'INSERT INTO myTableName ([myFirstParameter]) VALUES (' + 
    @myFirstParameter + ')'

This assumes the parameter has a string value. If not, you have to cast it, like cast(@myFirstParameter as varchar(128)).

The second way is to use sp_executesql instead of exec:

exec sp_executesql 
    N'INSERT INTO myTableName ([myFirstParameter]) VALUES (@myFirstParameter)',
    N'@myFirstParameter varchar(128)',
    @myFirstParameter = @myFirstParameter;

answered Jul 25, 2012 at 20:22

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

1 Comment

Eric Over a year ago

Thank you! I used a modified version of your second method and it worked like a charm.

Collectives™ on Stack Overflow

Retrieve parameter value from parameter name as a string in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related