SQL Server 2012 Using Declared Variables in a Join

Question

I'm quite new to SQL Server so hopefully this makes sense :)

I'm trying to declare variables to be used in an INNER JOIN.

If you take a look at my code, you'll see what I'm trying to do, without me needing to go into too much detail. Let me know if you need more info. Is that syntax possible?

EDIT: See new attempt below

    --State - If suburb/postcode, could use postcode lookup
Declare @Missing as nvarchar(255),
 @MissingUpdate as nvarchar(255),
 @MatchA as nvarchar(255),
 @MatchB as nvarchar(255),
 @Reason as nvarchar(255);

Set @Missing = '[StateEXPORT]'; -- field to update
Set @MissingUpdate = '[State]'; -- field in postcode lookup to pull in
Set @MatchA = '[PostcodeEXPORT]'; -- field in master field to match with
Set @MatchB = '[Pcode]'; -- field in postcode lookup to match with
Set @Reason = 'Contactable - Needs verificiation - @MissingUpdate taken from Lookup'; -- reason here

update [BT].[dbo].[test]
set @Missing = b.@MissingUpdate,    
    FinalPot =  @Reason
FROM [BT].[dbo].[test] a
INNER JOIN [BT].[dbo].[Postcode Lookup] b
ON a.@MatchA = b.@MatchB
where (@Missing is null or @Missing = '0') and [AddressSource] != ('Uncontactable')
GO

EDIT: SECOND ATTEMPT:

set @sql = 'update [BT].[dbo].[test] set ' + quotename(@Missing) + '= b.' +     quotename(@MissingUpdate) + ', FinalPot = ' + @Reason + 'FROM [BT].[dbo].[test] a INNER JOIN [BT].[dbo].[Postcode Lookup] b ON a.' + quotename(@MatchA) + ' = b.' + quotename(@MatchB) + 'where (' + quotename(@Missing) + 'is null or' + quotename(@Missing) + ' = 0 and [AddressSource] != "(Uncontactable)"'

exec (@sql)

Thanks for your help, Lucas

Answer 1

You can't use variable names as column names without dynamic SQL.

An example of a dynamic SQL query:

declare @ColumnName varchar(100) = 'col1'
declare @sql varchar(max)
set @sql = 'select ' + quotename(@ColumnName) + ' from dbo.YourTable'
exec (@sql)

Answer 2

No, this syntax is not possible, at least not directly: you need to specify the column name, not a string variable that has the name.

If you wish to decide the names of columns dynamically, you could make a SQL string that represents the statement that you wish to execute, and pass that string to EXECUTE command. You have to take extra care not to put any of the user-entered data into the generated SQL string, though, to avoid SQL injection attacks.

EDIT: The reason your second attempt may be failing is that you are passing names in square brackets to quotename. You should remove brackets from your variable declarations, like this:

Set @Missing = 'StateEXPORT'; -- field to update
Set @MissingUpdate = 'State'; -- field in postcode lookup to pull in
Set @MatchA = 'PostcodeEXPORT'; -- field in master field to match with
Set @MatchB = 'Pcode'; -- field in postcode lookup to match with