dynamic sql update

[camy123]

hi can some one help me pls ive written a peice of code to update.. using dynamic sql but it wont update ... does ne one know why example code is...

declare @sql nvarchar(2345)
set @sql = 'declare @i int'
+ ' while @i < 10'
+ ' begin'
+ ' update dupes set checked = 69 where col001 = ''{BFB0454B-BB66-4AA6-B183-0D9F8B9743F6}'''
+ ' set @i = @i + 1'
+ ' end'
exec sp_executesql @sql

 

[vongrunt]

You haven't assigned anything to @i. It is NULL initially, and loop never happens. Set it to 0 before loop.

Feel free to also remove dynamic SQL. In this case it is absolutely not necessary.


------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[camy123]

cheers sorry i know that this example does nto warrant Dynamic SQL i couldnt post my whole real code on here so i made a bad example sorry .. and thanks i put in
set @i= 0
and it worked.
but in my original code i have already instantiated this.. um............

 

[camy123]

here is my actual code ne body see what going wrong the

below if the part which is not updating -----
--- update dupes set checked = 1 where ' + @mastercolmap + '= @masterv' --------

the full code is below


declare @maxCount int
declare @minCount int
declare @innerloopmincount int
declare @innerloopmaxcount int
declare @tablename varchar(200)
declare @comparisonValue varchar(200)
declare @currentColumn varchar(200)
declare @mastercolmap varchar(200)
declare @duplicatecolmap varchar(200)
--declare @masterV varchar(200)
--declare @DuplicateV varchar(200)


select @maxCount = count(*) from #tablenames
set @minCount = 0

while @mincount < @maxcount
begin
print 'outer loop'
select top 1 @tablename = tablename from #tablenames where checked = 0
print @tablename
select @innerloopmaxcount = count(*) from #columnnames
set @innerloopmincount = 0

while @innerloopmincount < @innerloopmaxcount
begin
print 'inerloop'
select @comparisonValue = comparisoncol from #TableNames where tablename = + @tablename
select top 1 @currentColumn = colname from #columnnames where checked = 0
print @currentColumn
select @mastercolmap = mastercolmap from #TableNames where tablename =@tablename
select @duplicatecolmap = duplicatecolmap from #TableNames where tablename =@tablename

declare @sql nvarchar(2000)

set @sql = 'declare @masterV varchar(2000)'
+ ' Declare @Duplicatev varchar(2000)'
+ ' declare @minloopVal int'
+ ' declare @maxloopval int'
+ ' declare @Mval nvarchar(200)'
+ ' declare @dval nvarchar(200)'
+ ' declare @goto_v varchar(200)'
+ ' set @minloopval = 0'
+ ' select @maxloopval = count(*) from dupes'
+ ' update dupes set checked = 0'
+ ' while @minloopval < @maxloopval'
+ ' begin'
+ ' print @minloopval'
+ ' select @goto_v = colgoto from #columnnames where colname = ' + '''' + @currentColumn + ''''
+ ' select top 1 @masterV =' + @mastercolmap + ' from dupes where checked =''0'' '
+ ' select top 1 @Duplicatev=' + @duplicatecolmap + ' from dupes where checked =''0'''
+ ' select @masterV = convert(uniqueidentifier,@masterV)'
+ ' select @Duplicatev = convert(uniqueidentifier,@Duplicatev)'
+ ' print @masterV'
+ ' if @goto_v = 10 '
+ ' begin'
+ ' select @Mval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@masterv'
+ ' select @dval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@Duplicatev'
+ ' select @mval = isnull(@mval,''0'')'
+ ' select @dval = isnull(@dval,''0'')'
+ ' if @mval >= @dval or @mval = 1'
+ ' begin'
+ ' print ''no action taken'''
+ ' end'
+ ' else'
+ ' begin'
+ ' update ' + @tablename + ' set ' + @currentColumn + '= @dval where ' + @comparisonValue + ' = @masterv '
+ ' end '
+ ' end '
+ ' update dupes set checked = 1 where ' + @mastercolmap + '= @masterv'
+ ' delete from ' + @tablename + ' where ' + @comparisonValue + '= @Duplicatev'
+ ' set @minloopval = @minloopval + 1'
+ ' end'





exec sp_executesql @sql

update #columnnames set checked ='1' where colname = @currentColumn
set @innerloopmincount = @innerloopmincount + 1
end
update #tablenames set checked ='1' where tablename = @tablename
set @mincount = @mincount + 1
end