use Table Variable into Dynamic SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

use Table Variable into Dynamic SQL query

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-17 : 03:47:28

Hi
i want to use table variable into my stored procedure how it will be.
when i create my stored procedure it give error

Server: Msg 137, Level 15, State 2, Procedure USP_GetRecordsAtTimeFrame, Line 12
Must declare the variable '@TempUserID'.

--my storeprocedure is

create procedure USP_GetRecordsAtTimeFrame
@Fromime varchar(40),@Jrid varchar(30),@timeframe integer,@UIDTABLE varchar(30)
as
declare @fromTime varchar(100),@TableRow int,@count integer,@SQLQuery1 varchar(100),@sqlquery2 varchar(1000)
declare @TempUserID Table(timerecorded datetime,state varchar(250),Speed float,Place varchar(250))
set @fromTime=@Fromime
set @count=0
select @SQLQuery1='select count(*) from'+' '+@UIDTABLE
exec sp_executesql @SQLQuery1, N'@c int output', @TableRow output
while @count <=@TableRow
begin
select @sqlquery2='insert into '+' '+@TempUserID+' '+'select top 1 timerecorded,state,Speed,Place from '+' '+
@UIDTABLE+' '+' where jrid='''+@Jrid+''' and convert(datetime,Timerecorded,103) >=convert(datetime,'''+@fromTime+''',103)'
exec(@sqlquery2)
set @count=@count+1
set @fromTime=DATEADD(mi, @timeframe, convert(datetime,@fromTime,103))
end
select * from @TempUserID

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 05:59:13

try this :


create procedure USP_GetRecordsAtTimeFrame
	@fromime 	varchar(40),
	@jrid 		varchar(30),
	@timeframe 	integer,
	@UIDTABLE 	varchar(30)
as
begin
	declare @fromTime 	datetime,
		@TableRow 	int,
		@count 		integer,	
		@SQLQuery1 	varchar(100),
		@sqlquery2 	varchar(1000)

	create #TempUserID
	(
		timerecorded 	datetime,
		state 		varchar(250),
		Speed 		float,
		Place 		varchar(250)
	)

	set @fromTime 	= convert(datetime, @Fromime, 103)
	set @count	= 0

	select @SQLQuery1 = 'select @c = count(*) from'+' '+@UIDTABLE

	exec sp_executesql @SQLQuery1, N'@c int output', @TableRow output

	while @count <= @TableRow
	begin
		select @sqlquery2 = 	'insert into #TempUserID' + ' ' + 
					'select top 1 timerecorded, state, Speed, Place from ' + ' ' +
					@UIDTABLE + ' ' + 
					' where jrid = ''' + @Jrid + ''' and Timerecorded >= ''' + convert(varchar(30), @fromTime, 121) + ''''
		exec (@sqlquery2)

		set @count	= @count + 1
		set @fromTime	= DATEADD(minute, @timeframe, @fromTime)
	end
	select * from #TempUserID
end

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-03-17 : 06:12:39

The reason of the error (Must declare the variable '@TempUserID') you getting is that, the variable declaration and string execution is in different scope.

If you declare the variable in the same scope then this can work.
For example,

declare @mainstr nvarchar(100)
set @mainstr='Declare @dt datetime;select @dt=getdate();select @dt'
execute Sp_executesql @mainstr

Code snippet posted by KH is also correct and another way of doing it as he is replacing it using temp table

regards
sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-17 : 07:11:36

Hi
I execute the Code posted by KH but it give error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 22
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

The Error code is

select @SQLQuery1 = 'select @c = count(*) from'+' '+@UIDTABLE

exec sp_executesql @SQLQuery1, N'@c int output', @TableRow output

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 07:17:46

change @SQLQuery1 to nvarchar(4000)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-03-17 : 07:19:47

Change the datatype of @SQLQuery1 from varchar(100) to nvarchar(100).

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-17 : 07:22:15

Thanks My problem solved

Ranjeet Kumar Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources