Stored Procedure Not Performing Update

[RichyT]

Hi all,

I am having a bit of trouble with a stored procedure that I have written. The procedure takes data from my database and stores it in a temporary table. It then performs two updates on this temporary table and does a select on the temp table. If I run the same code in query analyzer, it works, however when I call the stored procedure (even from QA), it does not perform the updates. The reason I have used a temporary table is that each month there will be an extra column that needs extracting and so the table will grow (it will stop at some point but I currently don’t know when!)

I don’t really know much about stored procedures, so I was hoping that someone might be able to help. I can think of a few things that may work; putting the updates into a separate procedure and internally calling them from my procedure, changing the way I create the temp table; creating a permanent table that I add a column to each month. However I do not know if these things will work, or even if they are possible and do not really have time to try them all out.

If anyone can give me any advice or express their opinion on what the best alternative to try first would be, I would greatly appreciate it. If you would like a better explanation, please give me a shout.

 

[pascalsql]

Can you send your proc.

 

[SQLSister]

Most probable cause of what you are saying is that you used the Go command internally in the stored procedure. This would end the batch and thus end the stored procedure at the point where it occurred.

I am especially concerned about your statement that a new column would be added every month to your tables. This is an EXTREMELY POOR database design and indicates that what you probably really need is a related table.

 

[RichyT]

This is a cut down version of my procedure, I have added ... to fill in any gaps that I don't think are too important. I have used GO at the end of the procedure, and the update is between 2 selects that do get executed.

I think I worded the added column bit slightly wrong. My permanent tables will not have new columns added, it is my temp table that will. Basically I have used the temp table to simulate the extract that is my final goal. This extract contains months data in columns and each month there will be a new month and so a new column in the extract. I am aware that my code is probably not great but I don't understand why this code will work in QA and not in a stored procedure.


--Archive Extract
CREATE procedure uk_archive
@inpmonth varchar(2),
@inpyear varchar(4) AS

declare @pryear varchar(4)
declare @archint integer
declare @n as integer
declare @sql varchar(8000)
declare @sql2 varchar(8000)

set nocount on

set @pryear = cast(cast(@inpyear as integer)-1 as varchar(4))

--Archive
set @sql = 'select ' + char(39) + 'UK ' + char(39) + 'as ' + char(39) + 'Market' + char(39) +
', ufg.finname as ' + char(39) + 'manufacturer' + char(39) + ', ... as ' + char(39) + 'Model' + char(39)

set @archInt = 199701

While (@archInt < cast(@inpyear + '01' as integer))
begin
set @n = 1
While (@n < 13)
begin
set @sql = @sql + ', ...

set @archInt = @archInt + 1
set @n = @n + 1
end
set @archInt = @archInt + 88
end

set @n = 1
While (@n <= cast(@inpMonth as integer))
begin
set @sql = @sql + ', ...

set @archInt = @archInt + 1
set @n = @n + 1
end

set @sql = @sql + ' into #UKTemp from retailuk ru, ukmanufacts um, ukfingroup ufg where ru.mancode = um.mancode and um.fincode = ufg.fincode and'

set @archInt = 1998
set @sql = @sql + ' ...
While (@archInt <= cast(@inpYear as integer))
begin
set @sql = @sql + ' ...
end

set @sql = @sql + ')'

set @sql = @sql + ' group by ufg.finname, ... 'end'

set @sql2 = ' update #UKTemp set'
set @archint = @pryear + @inpmonth + 1
while (@archint < cast(@inpyear + @inpmonth as integer))
begin
set @sql2 = @sql2 + ' ... from #uktemp),'
if right(@archint, 2) <> 12
begin
set @archint = @archint + 1
end
else
set @archint = @archint + 89
end


set @sql2 = @sql2 + ' ... from #uktemp) '

set @sql2 = @sql2 + 'where manufacturer = ' + char(39) + 'OTHER' + char(39) + ' and &quot;HP Description&quot; = ' +
char(39) + 'No HP Available' + char(39)

set @sql2 = @sql2 + ' update #UKTemp set'
set @archint = @pryear + @inpmonth + 1
while (@archint < cast(@inpyear + @inpmonth as integer))
begin
set @sql2 = @sql2 + ' ...

end

set @sql2 = @sql2 + ' #UKTemp.m' + cast(@archint as varchar(6)) + ' = 0 '

set @sql2 = @sql2 + 'where manufacturer <> ...'No HP Available' + char(39)

set @sql2 = @sql2 + ' select * from #UKTemp'

execute(@sql + @sql2)
GO

 

[pascalsql]

Not sure but try this at the end:

set @sql = @sql + ' ' + @sql2 + ' select * from #UKTemp'
execute(@sql)

Reason:
1) you miss a space between sql and sql2
2) no expression allowed in execute

Hope it helps

 

[RichyT]

This does not work, because each variable can only be 8000 long (hence the use of two concatenated together). The space is there at the beginning of @sql2. I wasn't sure myself if you can use an expression in 'execute', however it seems to work when run from QA.