dynamic query sp_executesql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » dynamic query sp_executesql

dynamic query sp_executesql

Rate Topic

Display Mode

Topic Options

Author

Message

ratxamala

Posted Wednesday, February 25, 2009 6:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 27, 2013 9:35 AM
Points: 3, Visits: 5

---Works perfect for dynamic querys

IF OBJECT_ID('spTraerInfoParaFormato') IS NOT NULL
DROP PROCEDURE spTraerInfoParaFormato
GO

/*****************************************************************************************************
Procedimiento: spTraerInfoParaFormato
Creado por: Raul Bobadilla
Fecha: 11-02-2009
Descripcion: Trae la informacion de los datos dependiendo del tipo y del identificador unico
******************************************************************************************************/

create PROCEDURE [dbo].[spTraerInfoParaFormato]
(
@identificador nvarchar(50) = null,
@tipo int
)
as

---------------------DECLARACION DE TABLAS TEMPORALES--------------------
-------------------------------------------------------------------------
DECLARE @temptable table ( rowid int identity(1, 1),
campo nvarchar(100),
control nvarchar(100),
parametro nvarchar(100),
tabla nvarchar(100),
llave nvarchar(100),
valor nvarchar(500)
)

DECLARE @temptable1 table ( rowid1 int identity(1, 1),
valor1 nvarchar(500)
)

------------INSERCION DE VALORES EN LAS TABLAS TEMPORALES-----------------
--------------------------------------------------------------------------
insert into @temptable

select det.campo_det_datos_gral,
det.nombre_ctrl_det_datos_gral,
det.parametro_det_datos_gral,
dg.tabla_datos_gral,
dg.pk_datos_gral,
'' as valor
from t_datos_gral dg
inner join t_det_datos_gral det on dg.id_datos_gral = det.id_datos_gral
where dg.tipo_datos_gral = @tipo

declare @dynquery nvarchar(500)
declare @campo nvarchar(100)
declare @control nvarchar(100)
declare @parametro nvarchar(100)
declare @tabla nvarchar(100)
declare @llave nvarchar(100)

declare @count int
declare @irow int

set @count = @@rowcount
set @irow = 1

while @irow <= @count
begin
select @campo = campo,
@control = control,
@parametro = parametro,
@tabla = tabla,
@llave = llave
from @temptable
where rowid = @irow

set @dynquery = 'select ' + @tabla+'.'+@campo + ' from ' + @tabla + ' where ' + @tabla +'.'+ @llave + ' = ' + 'isnull(' + @identificador + ', ' + @tabla +'.'+ @llave +')'

-- PRINT @dynquery
insert into @temptable1
execute sp_executesql @dynquery

set @irow = @irow + 1
end

--------CURSOR PARA ACTUALIZAR EL VALOR DE LA TABLA FINAL---------
------------------------------------------------------------------
declare @valorfinal as nvarchar(500)
declare @rowidfinal as int

declare todos cursor for
select rowid1,valor1 from @temptable1

open todos

fetch next from todos into @rowidfinal, @valorfinal

while @@fetch_status = 0
begin

update @temptable set valor = @valorfinal where rowid = @rowidfinal

fetch next from todos into @rowidfinal, @valorfinal
end

close todos
deallocate todos

select campo, control, parametro, valor from @temptable

GO

Post #664190

« Prev Topic | Next Topic »

Permissions