Newbie: Assigning the query result to a variable.

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)

Newbie: Assigning the query result to a variable.

Author

Topic

zang
Starting Member

14 Posts

Posted - 2007-03-13 : 16:50:21

Example:
Variables declared:
@query is varchar (2000)
@result is integer

(I want to do something like this.......)
set @result = EXEC (@query)

PROBLEM:
The EXEC command is rejected
There is another option to resolve the problem?

@query contain a select who returns only an integer value, example “select count(*) from sales”

I need to put the proper sentence in a stored procedure

Sorry this simple question, I’m very newbie.
Thanks in advance,

Ariel.

JohnH
Starting Member

13 Posts

Posted - 2007-03-13 : 17:03:46

Example:

declare @result int
select @result = count(*) from sales

select @result

Does that do what you need?

John Hopkins

zang
Starting Member

14 Posts

Posted - 2007-03-13 : 18:19:44

Hi!, thanks for reply
There is a little difference, .
@query is a variable who received queries dynamically, for this reason is varchar (2000)

There is a cursor inside the stored procedure.
The cursor read a query_table that contains the query attribute.
In every record there is a different query stored in the query attribute who must be executed.
Later I do need execute this @query and that result must be assigned to the variable @result.

The problem is that I can not execute set @result = EXEC (@query)
In this example @query

Please let me now if I’m not clear enough.

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-13 : 18:29:43

You need to post your code in order for us to help.

Tara Kizer

http://weblogs.sqlteam.com/tarad/

zang
Starting Member

14 Posts

Posted - 2007-03-13 : 19:00:14

ok, there is the code:
Note: this is almost the entire code, I think is enough for you, otherwise let me know.
Tank’s for helping me, Ariel.

CREATE PROCEDURE PROC_SITD
------------------------------------------------------------------------------------------------
-- Procedimiento Almacenado
-- Acumula totales por cuenta dentro de un reporte en un rango de fechas
-- Recibe como parametros numero de reporte a calcular, fecha desde y fecha hasta el formato de la fecha debe ser - dd-mm-aaaa
-- El parametro numero de reporte es numero de reporte seleccionado por el usuario sobre el cual se desean obtener los totales
-------------------------------------------------------------------------------------------------
@reporte_numero as integer,
@Fecha_Desde as datetime,
@Fecha_Hasta as datetime
AS
----------------------------------------------------------------------------------------------------------------
-- CURSOR: Obtiene los totales de por cada cuenta de tabla reportes accediendo a los renglones de los asientos
-------------------------------------------------------------------------------------
-- declaramos las variables
declare @cuenta as decimal(17, 0), @importe as decimal(11, 2), @consulta as varchar(2000)
-- creacion cursor "cursor_sitd". El select debe contener sólo los campos a utilizar.
declare cursor_sitd cursor for
-- lee los registros de la tabla SITD_ReportesReng
-- obtiene por cada registro leido el importe
-- el importe se obtiene accediendo a la tabla sitd_ctb_renglon que contiene los renglones de los asientos
select cuenta, consulta from SITD_ReportesReng
where reporte_numero = @reporte_numero
--------------------------------------------------------------------------------------
-- apertura del cursor
open cursor_sitd

-- Avanzamos un registro y cargamos en las variables los valores encontrados en el primer registro
fetch next from cursor_sitd
into @cuenta, @consulta
-- @@fetch_status = 0 indica que el cursor se ejecuto correctamente,
-- cuando se llegue al final de la tabla clientes @@fetch_status devovera otro valor y saldra del bucle finalizando el proceso
while @@fetch_status = 0
begin
-- Para que el cursor actualice correctamente el where debe tener la condicion de que la fila leida se
-- corresponda con las variables almacenadas previamente, @cuenta identifica al mismo registro que se
-- esta leyendo
---------

--------- si el atributo consulta no es nulo ejecuta query con el contenido de consulta
--------- PROBLEMA: No se como asignar el resultado de EXEC (@consulta) a IMPORTE
--------- Teoria:
---------------- la idea es que si el campo consulta tiene un query lo ejecute y el resultado lo guarde en el importe.
--------------- si el campo consulta no tiene un query entonces el campo importe se obtine como es ahora(sumando los renglones de los asientos ---------

IF @Consulta is not null
---------- THERE IS THE PROBLEM, how to assing the result to @importe variable. --------------
set @importe = EXEC (@consulta)

where cuenta = @cuenta

-- Avanzamos otro registro

fetch next from cursor_sitd
into @cuenta, @consulta
end
-- cerramos el cursor
close cursor_sitd
deallocate cursor_sitd
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-13 : 19:10:24

I can barely read your code due to the amount of comments in it, but I think I know what you are trying to do in it.

declare @i int, @s nvarchar(200), @p nvarchar(500)
set @s = 'select @i = count(*) from sysobjects'
set @p = N'@i int output'
exec sp_executesql @s, @p, @i output
print @i

sp_executesql has much more flexibility for dynamic SQL.

Tara Kizer

http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources