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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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