CODEWITHSUNDEEP
sqlsql-server-2008
Scott Simontis
Scott Simontis

Reputation: 818

Specifying Column Name As A Parameter in SELECT statement?

I need to do something like this, but it always fails with 'Error converting data type varchar to int':

DECLARE @intParam INT
DECLARE @ColName VARCHAR(64)

SET @ColName='intcolumn'

SET @intParam = SELECT @ColName FROM myTable

How do I accomplish something like this? I can see the problem is that the SELECT statement simply returns the column name as a string, but I am not sure how to fix that. I am using SQL Server 2008R2.

Upvotes: 1

Views: 7657

Answers (2)

Filip Popović
Filip Popović

Reputation: 2655

You need to use dynamic sql:

  • build your dynamic SQL query (take a look at @SQL variable in sample below)
  • use output parameter to get value back from dynamic sql (take a look at @intParam and @intParam_out in sample below)
  • execute dynamic sql using sp_executesql
DECLARE @intParam INT
DECLARE @ColName VARCHAR(64)

SET @ColName='intcolumn'

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT @intParam_out = ' + @ColName + ' FROM myTable'
exec sp_executesql @SQL, N'@intParam_out int OUTPUT', @intParam_out = @intParam OUTPUT

Upvotes: 7

dcarneiro
dcarneiro

Reputation: 7180

Use Cast:

SET @intParam = SELECT cast(@ColName as int) FROM myTable

Upvotes: -2

Related Questions