sql server 2008 - Want to get selected value -


declare @qry varchar(max) declare @db varchar(25) declare @item varchar(25) declare @u_parentcode varchar(25)   set @u_parentcode ='cdm51306520' set @db ='marda_test' set @qry ='select @item =itemcode ' + @db + '.[dbo].[oitm]  itemcode=''' + @u_parentcode +'''' execute (@qry) print @item 

am getting error 'must declare scalar variable "@item".'

the dynamic sql executes in different context context of code, there isn't @item variable declared on there.

you need use sp_executesql contains mechanism map variables variables within block. syntax obtuse, it's obvious when used it.

declare @qry nvarchar(max) declare @db varchar(25) declare @outsideitem varchar(25) declare @u_parentcode varchar(25)  set @u_parentcode ='cdm51306520' set @db ='marda_test' set @qry ='select @insideitem =itemcode ' + @db + '.[dbo].[oitm]  itemcode=''' + @u_parentcode +''''  execute sp_executesql @qry, n'@insideitem varchar(25) output', @insideitem = @outsideitem output print @item 

the first parameter query, same exec().
second parameter declaration of parameters used in query, in case @insideitem.
third (and subsequent) parameter mapping says inside parameter should mapped outside parameter. output clause (used both on variable declaration , on mapping) designator that parameter used output value of query.


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -