Query using Variable returns always the Variable value

Question

I have created the following procedure to finally get the value (and not the column name at the end). However if using just the declared @ColumnName in the last query I just see the column name 'XXX' and not the value for XXX at that row.

I have the following:

DECLARE @ColumnName AS VARCHAR(50)
SET @ColumnName = (select column_name    FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'History' and  ORDINAL_POSITION = 2 )

SELECT   @ColumnName from kat.[dbo].[History]

What I would like to get is the following results :

select XXX from kat.[dbo].[History]

Even the @ColumName has the value of XXX this is not working.

In the left I get my result (always the name of the column, and it the right is the desired value)

1-XXX, The value from XXX
2-XXX, the next value from XXX
3-XXX, the next value from XXX

Many thanks in advance,

Kat

I assume History has many rows. About which value of XXX are you interested in? — Radim Bača
– Radim Bača, Commented Oct 17, 2017 at 8:16
Because @ColumnName is a varchar it will always return a string. In SQL Server there are no column variable types. Instead, you need to use a technique called dynamic SQL (source MS Docs). — David Rushton
– David Rushton, Commented Oct 17, 2017 at 8:31

Jaydip Jadhav · Accepted Answer · 2017-10-17 08:17:20Z

1

You need dynamic query

EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] ')

answered Oct 17, 2017 at 8:17

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

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

4 Comments

Katherine99 Over a year ago

Can I also use the result of the dynamic query in order to insert values from this query? For example insert into Table values ( ( EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] ')). Is this possible?

Jaydip Jadhav Over a year ago

Yes, Just need to use proper syntax like INSERT TableName(ColumnName1) EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] ')

Katherine99 Over a year ago

Thanks again, is it also compatible when inserting multiple columns ? For example : INSERT TableName(ColumnName1, ColumnName2) EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] '), datum from kat.[dbo].[History] . With just the insert of the dynamic query it works but wiith more value it does not compile

Jaydip Jadhav Over a year ago

You need to select same number of columns columns in dynamic query as well.

Collectives™ on Stack Overflow

Query using Variable returns always the Variable value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related