February 13, 2014 at 4:15 am
The EXEC (@SQL) starts a new transaction. Within this transaction the variable has to be declared.
declare @var varchar(max) = '@var1',
@sql varchar(max)
declare @var1 varchar(max)
print @var
set @sql='declare '+@var+' varchar(max);set '+@var+' = ''name'''
print @sql
exec(@sql)
February 13, 2014 at 4:52 am
Hi,
We tried this also, but variable "var1" is not retaining the value "name", print @var1 is returning NULL.
declare @var varchar(max) = '@var1',
@sql varchar(max)
declare @var1 varchar(max)
print @var
set @sql='declare '+@var+' varchar(max);set '+@var+' = ''name'''
print @sql
exec(@sql)
print @var1
Please suggest how to retain that value.
February 13, 2014 at 5:11 am
Scope.
@var1 is never being set within the scope of your code.
You need to read on sp_executesql and use that.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 13, 2014 at 5:51 am
We tried using sp_executesql also, getting below error
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
declare @var nvarchar(max) = '@var1',
@sql nvarchar(max)
declare @var1 nvarchar(max)
DECLARE @dbstatus varchar(500)
print @var
--set @sql='declare @var1 varchar(max) set '+@var+' = ''name'' print @var1 '
set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'' print '+@var
print @sql
--exec(@sql)
EXECUTE sp_executesql @sql, '@dbstatus varchar(500) output', @dbstatus output
print @dbstatus
Please suggest...
February 13, 2014 at 7:34 am
Change your D-ECLARE @dbstatus varchar(500) and @dbstatus varchar(500) output to nvarchar(500)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 8:07 am
Hi,
After changing to nvarchar also same error...
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
???
February 13, 2014 at 8:15 am
My mistake, Add N in front of the @dbstatus nvarchar(500) output - like so (you will need to rmeove the spaces in "d eclare"):d eclare @var nvarchar(max) = '@var1',
@sql nvarchar(max)
declare @var1 nvarchar(max)
D ECLARE @dbstatus varchar(500)
print @var
--set @sql='declare @var1 varchar(max) set '+@var+' = ''name'' print @var1 '
set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'' print '+@var
print @sql
--exec(@sql)
EXECUTE sp_executesql @sql, N'@dbstatus nvarchar(500) output', @dbstatus output
print @dbstatus
Results:
@var1
declare @var1 nvarchar(max) set @var1 = 'name' print @var1
name
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 9:35 am
Hi Thank you for your response,the error was gone but the the output "name" is coming from "set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'' print '+@var " not from "print @dbstatus"... finally we need the output value needs to be retained in @dbstatus ...
so the below script should give "name" as output..but its returning NULL..
"name" is not getting assigned to @dbstatus.
declare @var nvarchar(max) = '@var1',
@sql nvarchar(max)
declare @var1 nvarchar(max)
DECLARE @dbstatus varchar(500)
set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'''
EXECUTE sp_executesql @sql, N'@dbstatus nvarchar(500) output', @dbstatus output
print @dbstatus
it shoould return me "name" where as its returing NULL
Please Suggest
February 13, 2014 at 10:09 am
I don't believe this is possible due to "scope" (as someone already pointed out). Your variable @dbstatus isn't in scope when the sp_executesql is executed therefore cannot be assigned a value at that time. Hence why it is returned NULL.
I'm sorry. perhaps another user has a better explanation or work-around :crazy:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 10:47 am
Minnu (2/13/2014)
Hi Thank you for your response,the error was gone but the the output "name" is coming from "set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'' print '+@var " not from "print @dbstatus"... finally we need the output value needs to be retained in @dbstatus ...so the below script should give "name" as output..but its returning NULL..
"name" is not getting assigned to @dbstatus.
declare @var nvarchar(max) = '@var1',
@sql nvarchar(max)
declare @var1 nvarchar(max)
DECLARE @dbstatus varchar(500)
set @sql='declare '+@var +' nvarchar(max) set '+@var+' = ''name'''
EXECUTE sp_executesql @sql, N'@dbstatus nvarchar(500) output', @dbstatus output
print @dbstatus
it shoould return me "name" where as its returing NULL
Please Suggest
First, please go read Books Online on how to use sp_executesql properly. You are traveling down the wrong path for what, I think, you are trying to do. BTW, what exactly are you trying to do?? I have a sneaky suspicion that dynamic SQL may not be your best option.
For starters, stop trying to concatenate the strings together. See above. Read books online.
AS I said before, you cannot assign the NAME of a variable to a variable, and expect to get the VALUE back. This is out of scope.
Here is the simple example.
DECLARE @sql nvarchar(max)
DECLARE @Var1 nvarchar(max)
SET @Var1 = 'Foo'
SET @sql = 'SELECT something FROM sometable WHERE somefield = @Var1'
EXEC sp_executesql
@statement = @sql,
@Parm1 = N'@Var1 nvarchar(max)',
@Var1 = @Var1
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply