sql error with dynamic insert - SQL Server Forums

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

SQL Server Development (2000)

sql error with dynamic insert

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-01 : 10:07:13

i am getting an error of the
variable is not defined but I have defined
them.

declare @strsql varchar(200)
create table #temp (time varchar(20), count int)
declare @i varchar(2)
declare @t1 varchar(20)
declare @v1 varchar(2)
declare @t2 varchar(20)
declare @v2 varchar(2)

set @t1='8:00:00'
set @v1=5
set @t2='9:00:00'
set @v2=3
set @i=1

while @i <= 2
begin
set @strsql='insert #temp values (@t'+@i+', @v'+@i+')'
print @strsql
exec (@strsql)
set @i=@i+1
end

drop table #temp

slow down to move faster...

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-02-01 : 10:29:12

quote:
set @strsql='insert #temp values (@t'+@i+', @v'+@i+')'

You need to use this:
insert #temp values (@t1,@v1)
insert #temp values (@t2,@v2)

Edited by - andre on 02/01/2002 10:31:37

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-01 : 10:33:16

I don't want @t, @v as variables.

@t1=something
@t2=somethng

I want to increment the insert as @i increments.

while @i < whatever
begin

insert @i....

end

slow down to move faster...

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-02-01 : 11:01:29

When I ran your code in Query Analyzer, the output of


set @strsql='insert #temp values (@t'+@i+', @v'+@i+')'
print @strsql
exec (@strsql)

produces

insert #temp values (@t1, @v1)
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@t1'.

When you build a dynamic SQL string, you can't have any @'s in it. You can if you use static SQL like this:


insert #temp values (@t1,@v1)
insert #temp values (@t2,@v2)

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-01 : 11:30:39

Ok,

I was just wondering if I could do it instead of static
assignment of insert .....

thanks for the time and knowledge.

;)

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources