SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dynamic query sp_executesql


dynamic query sp_executesql

Author
Message
ratxamala
ratxamala
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search