Problem with a sp

[pap]

Please see my attachment.

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.

[YuckFou]

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

i don't quite understand what you are trying to do, but if @Clsxx and @Clqxx are names of columns in table sffpoxx0, you don't need to declare so many variables for each column.
declare only the "xx" variables @poqxx , @Clqxx , @posxx , @Clsxx

[Claire]

you should declare variable when execute the dynamic SQL.

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


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

set @sql='declare @'+convert(varchar(7),@clqxx)+
case when convert(int,right(@clqxx,3))<=33 then ' varchar(40), ' else ' numeric(5,1),' end
+' @'+@clsxx+case when convert(int,right(replace(@clsxx,' ',''),3))<=33 then ' varchar(40) ' else ' numeric(5,1)' end
+' update sffpoxx0 set ' + @poqxx + '= @' + convert(varchar(7), @Clqxx) +
' where ' + @posxx + '= @' + @Clsxx
exec(@sql)
set @contador=@contador+1
end

[pap]

Thanks for your help

When "you" declare the @clqxx (in the update) that will content another variable for example @Col033 it means that this will, at first be empty isn't it.

If so, I have a problem because this variable is also declared in the beggining of the sp, this variable will have the data of a temporary table.

Well, I will put here all the code, thanks

[Claire]

In this case you should include everything in your dynamic sql, or discard your current logic with new way of thinking.

No matter what, dynamic sql needs to have its variable declaration, whatever you define outside of the dynamic sql is not going to be known by query analyzer when it's executing the statement.