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() ?
