September 28, 2018 at 12:25 pm
The two examples below are a simplified version of an error I'm running in to. The top version runs fine, the bottom version where I'm using exec keeps throwing errors. I think it's the concatenation with 1 and the parameter. I've got other parts of my code where I'm using exec and concatenating code with parameters that works just fine, so I'm scratching my head on this one. Any tips greatly appreciated.
works fine:
Declare @testvar Varchar(MAX)
select top 1 @testvar=NameSchema from #TempCommonMatchFormatted
select @testvar
throws error:
Declare @testvar Varchar(MAX)
exec('select top 1 '+@testvar+'=NameSchema from #TempCommonMatchFormatted
select '+@testvar)
error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
September 28, 2018 at 12:51 pm
scotsditch - Friday, September 28, 2018 12:25 PMThe two examples below are a simplified version of an error I'm running in to. The top version runs fine, the bottom version where I'm using exec keeps throwing errors. I think it's the concatenation with 1 and the parameter. I've got other parts of my code where I'm using exec and concatenating code with parameters that works just fine, so I'm scratching my head on this one. Any tips greatly appreciated.works fine:
Declare @testvar Varchar(MAX)select top 1 @testvar=NameSchema from #TempCommonMatchFormatted
select @testvar
throws error:
Declare @testvar Varchar(MAX)
exec('select top 1 '+@testvar+'=NameSchema from #TempCommonMatchFormatted
select '+@testvar)error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
That is because in the second you are putting the contents of the variable @testvar in the string.
Even if you were putting the string @testvar into the string it would fail as the variable isn't declared in the dynamic SQL that would be executed.
September 28, 2018 at 1:08 pm
exec('declare @testvar nvarchar(128); select top (1) @testvar = NameSchema from #TempCommonMatchFormatted; select @testvar as testvar')
If you want to return a value(s) to a variable(s), the best way is using sp_executesql:
Declare @sql nvarchar(4000)
Declare @testvar nvarchar(128)
Set @sql = 'select top 1 @testvar = NameSchema from #TempCommonMatchFormatted'
Exec master.sys.sp_executesql @sql, N'@testvar nvarchar(128) OUTPUT', @testvar OUTPUT
Select @testvar
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 28, 2018 at 1:54 pm
Thank you! Been bugging hunting that one for a while.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply