problem with a sp - 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

Transact-SQL (2000)

problem with a sp

Author

Topic

pap
Starting Member

13 Posts

Posted - 2004-03-02 : 05:41:34

I have created a sp but for some reason in this part of the code

set @sql='update sffpoxx0 set ' + @poqxx + '= @' + convert(varchar(7), @Clqxx) + ' where ' + @posxx + '= @' + @Clsxx
exec (@sql)

a error occurs: please declare variable @Col033

What I intent (I don't know if this is possible) is, in the variables @Clqxx and @Clsxx have another variable @Col033, @Col034, etc.

Can anyone help me

Thanks.

Please see code below:

declare @res int, @cursor int, @variable varchar(5), @status varchar(2), @status1 int, @i int, @text varchar(5000), @stock_generico int
declare @hub varchar(10), @col02 varchar(20), @cartoon varchar(20), @data varchar(8), @time varchar(8), @ship varchar(10)
declare @sql varchar(5000), @posxx varchar(5), @poqxx varchar(5), @Clsxx varchar(7), @Clqxx varchar(7), @contador int

declare @Col001 varchar(9), @Col002 varchar(10), @Col003 varchar(13), @Col004 int, @Col005 varchar(40), @Col006 varchar(3), @Col007 varchar(3)
declare @Col008 varchar(4), @Col009 varchar(4), @Col010 varchar(4), @Col011 varchar(4), @Col012 varchar(4), @Col013 varchar(4), @Col014 varchar(4)
declare @Col015 varchar(4), @Col016 varchar(4), @Col017 varchar(4), @Col018 varchar(4), @Col019 varchar(4), @Col020 varchar(4), @Col021 varchar(4)
declare @Col022 varchar(4), @Col023 varchar(4), @Col024 varchar(4), @Col025 varchar(4), @Col026 varchar(4), @Col027 varchar(4), @Col028 varchar(4)
declare @Col029 varchar(4), @Col030 varchar(4), @Col031 varchar(4), @Col032 varchar(4), @Col033 varchar(7), @Col034 numeric(5,1), @Col035 numeric(5,1)
declare @Col036 numeric(5,1), @Col037 numeric(5,1), @Col038 numeric(5,1), @Col039 numeric(5,1), @Col040 numeric(5,1), @Col041 numeric(5,1), @Col042 numeric(5,1)
declare @Col043 numeric(5,1), @Col044 numeric(5,1), @Col045 numeric(5,1), @Col046 numeric(5,1), @Col047 numeric(5,1), @Col048 numeric(5,1), @Col049 numeric(5,1), @Col050

numeric(5,1)
declare @Col051 numeric(5,1), @Col052 numeric(5,1), @Col053 numeric(5,1), @Col054 numeric(5,1), @Col055 numeric(5,1), @Col056 numeric(5,1), @Col057 numeric(5,1), @Col058

varchar(1)

set @contador=1
while @contador<26
begin
if @contador<10
begin
set @posxx='pos0' + convert(varchar(1),@contador)
set @poqxx='poq0' + convert(varchar(1),@contador)
end
else
begin
set @posxx='pos' + convert(varchar(2),@contador)
set @poqxx='poq' + convert(varchar(2),@contador)
end
if @contador+7<10
begin
set @Clsxx='Col00' + convert(varchar(1),@contador+7)
end
else
begin
set @Clsxx='Col0' + convert(varchar(2),@contador+7)
end
set @Clqxx='Col0' + convert(varchar(2),@contador+32)

set @sql='update sffpoxx0 set ' + @poqxx + '= @' + convert(varchar(7), @Clqxx) + ' where ' + @posxx + '= @' + @Clsxx
exec (@sql)
set @contador=@contador+1
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 05:50:37

You are declaring the variable in the SP but trying to reference it in another batch (dynamic sql)

To see the problem run

declare @i int
exec ('select @i')

What are you trying to do here?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources