error with sp_executesql - 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)

error with sp_executesql

Author

Topic

Hijack
Starting Member

4 Posts

Posted - 2009-06-05 : 18:31:25

Good evening! and thanks in avance. Ive been reading the forum for a while and here i am.

The problem comes with the execution of a string called by sp_executesql

The code is extense and kinda boring (sorry) that's why id like to tell you a little bit about the code.
It´s a SP wich's gonna be executed periodically. The sp needs 3 variables wich are sector, unidad and pcrc.
Then i declare a string to be executed. This string is gonna compare
data from a table with the same one with the previous actualization (tmo_total and tmo_total_copy). The goal is to get the "new" lines in the tmo_Total table wich is the one that contains the last information.
Ive tested the code itself and it works perfectly... the problem came when i decided (because i need it) to make the sp dinamic... those 3 variables are gonna change from time to time.
First step is to compare what i jsut said... that's great.. works... second step is to update a table within the server... that's where the problem lies. Allow me to be clear on this... the first step and the second step (update) worked just fine when i wrote the 3 variables, the problem came when i started using the sp_executesql because of those variables.

Here it goes.

------------------------------------------------------------------

CREATE PROCEDURE SP_TIEMPO_MEDIO_XHORA
@sector nvarchar(20),
@unidad nvarchar(20),
@pcrc nvarchar(20)
as

declare @cadena nvarchar(3000)
declare @select nvarchar(1000)
declare @update nvarchar(1000)

set @update = 'update tiempo_medio set ingresos_del_dia = ingresos_del_dia + @bandera, total = ingresos_del_dia + inicio WHERE fecha = (CONVERT (nvarchar(15), getdate(),103)) and un = ''' +@unidad+ ''' and sector = ''' +@sector+ ''' and pcrc = ''' +@pcrc+ ''''
set @select = 'select linea, fecha_instancia from tmo_total where unidad = ''' +@unidad+ ''' and bo = ''' +@sector+ ''' and gestion = ''' +@pcrc+ ''''
--string starts
set @cadena = 'declare @linea nvarchar(10), @fecha datetime, @linea_v nvarchar(10), @fecha_v datetime
DECLARE @Cursor CURSOR
declare @bandera int
set @bandera = 0
set @cursor = cursor for ' +@select+ '
open @cursor
FETCH NEXT FROM @cursor into @linea, @fecha
WHILE @@FETCH_STATUS = 0
BEGIN
set @linea_v = (select linea from tmo_total_copia where linea = @linea and fecha_instancia = @fecha)
set @fecha_v = (select fecha_instancia from tmo_total_copia where linea = @linea and fecha_instancia = @fecha)
if (@linea_v IS NULL and @fecha_v IS NULL)
begin
set @bandera = @bandera + 1
end
FETCH NEXT FROM @Cursor
INTO @linea, @fecha
END;

EXEC sp_executesql ' +@update+ ' --this is the error it seems

CLOSE @Cursor;
DEALLOCATE @Cursor;'
--end of stringa
EXEC sp_executesql @cadena
GO

------------------------------------------------------------------

I call the sp this way

exec sp_tiempo_medio_xhora bo, negocios, diag
--bo, negocios, diag are the imputs

this is the error

Msg 201, Level 16, State 10, Procedure sp_executesql, Line 20
Procedure 'sp_executesql' expects parameter '@statement', which was not supplied.

Id googled this error but nothing id been tried worked. It'd be usefull to let you know that i've tried a few more things in order to get this sp done. For example i tried declaring and setting the string update INSIDE the main string... again, problems..

Any help will be very appreciated!! Thanks a lot

Dan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 21:01:55

try
EXEC sp_executesql @update

KH
[spoiler]Time is always against us[/spoiler]

Hijack
Starting Member

4 Posts

Posted - 2009-06-06 : 10:38:41

Thanks, I've tried!
the error comes:
Msg 137, Level 15, State 2, Line 22
Must declare the variable '@update'.

@update is declared outside the string @cadena, the error makes sence. Ive tried also to declare and set @update inside @cadena but it wouldnt work anyway.

Hijack
Starting Member

4 Posts

Posted - 2009-06-06 : 10:46:01

This may help a little bit..
the whole process runs only for the update part.... so if you look at the code a little bit you'll get the idea that the variable @bandera is the important one... i could update outside the string @cadena.. and that would be the end of the trouble... but the problem is that i cant get the value of @bandera outside de string @cadena...
i cant manage to put it on a output value... or how to set the value @bandera to another variable outside @cadena...

that may clear a few minds.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:14:23

i cant see a need of using dynamic sql here.
suggest you read below before proceeding with dynamic query for this.

http://www.sommarskog.se/dynamic_sql.html

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 09:54:08

First step:
try the sp below
Second step then would be to see if this can be done without using a cursor

CREATE PROCEDURE SP_TIEMPO_MEDIO_XHORA 
@sector nvarchar(20),
@unidad nvarchar(20),
@pcrc nvarchar(20)
as

declare @linea nvarchar(10), @fecha datetime, @linea_v nvarchar(10), @fecha_v datetime
DECLARE @Cursor CURSOR
declare @bandera int
set @bandera = 0
set @Cursor = cursor for select linea, fecha_instancia 
                           from tmo_total 
                          where unidad = @unidad
                            and bo = @sector
                            and gestion = @pcrc 
open @cursor
FETCH NEXT FROM @cursor into @linea, @fecha
WHILE @@FETCH_STATUS = 0
BEGIN
set @linea_v = (select linea from tmo_total_copia where linea = @linea and fecha_instancia = @fecha)
set @fecha_v = (select fecha_instancia from tmo_total_copia where linea = @linea and fecha_instancia = @fecha)
if (@linea_v IS NULL and @fecha_v IS NULL)
begin 
set @bandera = @bandera + 1
end
FETCH NEXT FROM @Cursor
INTO @linea, @fecha
END;

update tiempo_medio 
set ingresos_del_dia = ingresos_del_dia + @bandera, 
    total = ingresos_del_dia + inicio 
WHERE fecha = (CONVERT (nvarchar(15), getdate(),103)) 
  and un = @unidad 
  and sector = @sector
  and pcrc = @pcrc 

CLOSE @Cursor;
DEALLOCATE @Cursor;
GO

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 13:32:35

i think you need only this


CREATE PROCEDURE SP_TIEMPO_MEDIO_XHORA 
@sector nvarchar(20),
@unidad nvarchar(20),
@pcrc nvarchar(20)
as

update  
set ingresos_del_dia = ingresos_del_dia + t.CountVal, 
    total = ingresos_del_dia + inicio 
FROM tiempo_medio tm
JOIN(
select tt.unidad,tt.bo,tt.gestion,COUNT(*) AS CountVal
from tmo_total tt 
left outer join tmo_total_copia ttc
on ttc.linea =tt.linea 
and ttc.fecha_instancia = tt.fecha_instancia 
where ttc.linea is null 
and ttc.fecha_instancia is null
GROUP BY tt.unidad,tt.bo,tt.gestion
)t
ON t.unidad=tm.un
AND t.bo=tm.sector
AND t.gestion=tm.pcrc
WHERE fecha = (CONVERT (nvarchar(15), getdate(),103)) 
  and tm.un = @unidad 
  and tm.sector = @sector
  and tm.pcrc = @pcrc 
GO

Hijack
Starting Member

4 Posts

Posted - 2009-06-08 : 08:31:41

visakh16: Thanks a lot! you're completely right... no need for a cursor and no need for dynamic proc. Not only helped me with the code, but also opened my mind a bit more about planning before coding... thanks a lot, really.

webfred: thanks to you too! havent tested the code 'cause it's working by now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources