Cannot set a Variable from a select statement that contains a variable? Help Please

I am trying to set a vaiable from a select statement

DECLARE @.VALUE_KEEP NVARCHAR(120),

@.COLUMN_NAME NVARCHAR(120)

SET @.COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @.VALUE_KEEP = (SELECT @.COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @.VALUE_KEEP

PRINT @.COLUMN_NAME

RESULTS

-

FirstName <--@.VALUE_KEEP

FirstName <--@.COLUMN_NAME

SELECT @.COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @.COLUMN_NAME variable?

Any help greatly appreciated!

Your second SET statement is just applying the value of @.COLUMN_NAME to @.VALUE_KEEP. Based on the hardcoding in the first set statement I'm not sure exactly what you would be achieving with the variable use. If you are just trying to select a column dynamically from a given table you would need to use dynamic sql. Syntax something like...

DECLARE @.myVariable varchar(50), @.sql nvarchar(max) --or 4000 if using SQL 2000

SET @.myVariable = 'FirstName'

SET @.sql = 'SELECT ' + @.myVariable + ' FROM dbo.myTable WHERE myColumn = myColumn'

EXEC sp_executesql @.sql

That said, I would be very wary of using this approach in an application as there are security and maintainability issues with dynamic sql.

|||

I am passing this to coalesce()

my goal is to dynamically loop through the columns using coalesce() that is part of my stored procedure for de-duplicating a database. Since the columns can change I wanted to pull the columns and table dynamically.

I compare the records of the duplicates to update any null fields and want to do something like

coalesce(@.Record_Being_Kept, @.Record_Being_Replaced)

so two things...

I want the values in the coalesce ('Brent', 'Brent) when it is comparing the firstnames "Obviously this is much more applicable with the phone and email info" to Merge the Dupes.

I don't know if there is a way to pass EXEC sp_executesql @.sql to coalesce() ?