Dynamic Sql use of variables within subselect

Question

I am trying to use variables within my update statement but for some reason I don't understand it gives an error.

The following code works:

declare @sql nvarchar(max)
set @sql='update valuetable set waarde=(select top 1 firstname from DimEmployee where firstname not like ''%[0-9]%'' )'
exec (@sql)

If I change firstname and DimEmployee to variables using the following code:

declare @column nvarchar(max);
declare @table nvarchar(max);
declare @sql nvarchar(max)
set @column='firstname';
set @table='dbo.dimEmployee'
set @sql='update valuetable set waarde=(select top 1 '+ @column + 'from '+@table+' where '+@column+' not like ''%[0-9]%'')'
exec (@sql)

it gives the following error: Incorrect syntax near '.' on line 1

Could somebody explain to me what I'm doing wrong and if there's a fix for my problem?

Gordon Linoff · Accepted Answer · 2018-08-13 10:10:37Z

1

You should print out the SQL to see what you are actually running. For instance, it is obvious that the lack of space before from is an issue:

set @sql = '
update valuetable
    set waarde = (select top 1 '+ @column + ' from ' + @table + ' where ' + @column + ' not like ''%[0-9]%'')
';

answered Aug 13, 2018 at 10:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Dynamic Sql use of variables within subselect

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related