January 28, 2002 at 3:16 am
hi,
i have a query in a Store Procedure which changes as per the parameters passed to it...
following is a extract/example:
--------------------------------------
declare @vSTR varchar(100), @vVAL int , @dGetDT datetime , @bVAL binary
set @bVAL = 1
if (@bVAL = 0 )
SET @vSTR = 'SELECT @dGetDT = getdate()'
else
SET @vSTR = 'SELECT @dGetDt = ''2000-02-24 00:00:00.000'''
exec (@vSTR)
--------------
This gives following error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@dGetDt'.
-------------------------------------
can sombody help????
him
January 28, 2002 at 3:56 am
If the declarations are in the exec statement then it will work ok:
declare @vSTR varchar(100), @bVAL binary
set @vSTR = 'declare @vVAL int , @dGetDT datetime '
set @bVAL = 1
if (@bVAL = 0 )
SET @vSTR = @vSTR + 'SELECT @dGetDT = getdate()'
else
SET @vSTR = @vSTR + 'SELECT @dGetDt = ''2000-02-24 00:00:00.000'' select @dGetDt'
exec (@vSTR)
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 29, 2002 at 4:39 am
thanx paul for ur quick reply.
But the problem persist.. The scope of the local variable "@dGetDt" ends in the exec statement itself...
I want the value of @dGetDT to do some further processing then how do i get it?
--------------------------------------
If the declarations are in the exec statement then it will work ok:
declare @vSTR varchar(100), @bVAL binary
set @vSTR = 'declare @vVAL int , @dGetDT datetime '
set @bVAL = 1
if (@bVAL = 0 )
SET @vSTR = @vSTR + 'SELECT @dGetDT = getdate()'
else
SET @vSTR = @vSTR + 'SELECT @dGetDt = ''2000-02-24 00:00:00.000'' select @dGetDt'
exec (@vSTR)
Paul Ibison
January 29, 2002 at 5:50 am
I'd insert the logic intop a separate sp:
create procedure myproc
@dGetDT datetime output,
@bVal bit = 0
as
begin
set nocount on
if (@bVAL = 0 )
set @dGetDT = getdate()
else
set @dGetDt = '2000-02-24 00:00:00.000'
end
go
declare @dGetDT datetime
declare @bVAL binary
set @bVAL = 1
exec myproc @dGetDT output, @bVal
select @dGetDT
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
February 5, 2002 at 2:38 pm
Just a note as a why. @dGetDT is a local variable and when you built the string and executed the string variable it created another process not in the same scope as your declared varibale, Thus to the executed string the variable was never created. The previous method keeps everything in the same process scope.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply