Physical Therapy Aide Sample Cover Letter

How to send a table to a Stored Procedure

In recent days I ran into a problem when setting a screen that some processing of Payroll, and it is typical that the user does not give you the complete or complete cycle of the process, my screen working properly , I suddenly completely change the requirements:

JefaNomina .- "Sergio, requirements changed, now the increase is not for everyone as you apply it on the screen, you have to put filters for different fields. "
Sergio .-" Quee, is a big change, that is, my query works correctly calculated the amount of "ProcesoNomina" of all employers, but with the same percentage for all "
JefaNomina .-" Ok, now employees can be assigned different rates, not everyone is estimated, has some A fixed amount will increase by percentage and other "
Sergio .-" And now as I do that, I'll have to make a new query "

Problem Statement:

for several days Make a SQL query that performs the calculation of "ProcesoNomina" about 10.000 employees in an automated manner, with a percentage increase for all, now the screen has to validate them if and which are not calculated if the amount or percentage rate and if what the different values \u200b\u200bfor each group of employees.

By making this change practically I have to redo the query, it should be noted that it is a very complex query.

As
resolved without removing the first query:
Through SQL Server 2005 to do an Inner join between the result of the first consultation and the new filters to be stored in a temporary table, this not to perform a new search. Hence the big question "How to send a table to a Stored Procedure"
a stored procedure receives integer values, text, etc., But not a table.
The solution is to send a table in XML format to the stored Procedure, read in the Stored and copy it to a temporary table.

like sending a table to a Stored Procedure:
Step 1: Regardless the programming language being used convert your table to XML format

Step 2: Receive XML document as a Sture Procedure
XML data type CREATE PROCEDURE [dbo]. [SP_RecibirTablaXML] (@ XmlDocument XML)

Step 3: Create a temporary table where the query stored XML document. CREATE TABLE # TTemporalTipoTrabajador
(Selected integer, integer Id_Tipo_Trabajador
,
Txt_Tipo_Trabajador varchar (30),
Porc_Incremento decimal (18.4),
Porc_Incremento_Despensa decimal (18.4),
Imp_Incremento_Despensa decimal (18.4), decimal Porc_Incremento_AyudaRenta
(18.4),
Imp_Incremento_AyudaRenta decimal (18,4))


Step 4: Create an internal representation of XML document received to allow for consultations through the OPENXML command DECLARE @ DocHandle


int EXEC @ DocHandle sp_xml_preparedocument OUTPUT, @ XmlDocument

Step 5: We perform OPENXML query through and tell you WHIT command through the fields we want to obtain, in addition to input the records to the temporary table.

INSERT INTO # TTemporalTipoTrabajador (Selected, Id_Tipo_Trabajador, Txt_Tipo_Trabajador, Porc_Incremento, Porc_Incremento_Despensa, Imp_Incremento_Despensa
, Porc_Incremento_AyudaRenta, Imp_Incremento_AyudaRenta)
SELECT *
FROM OPENXML (@ DocHandle, '/ NewDataSet / dtTipoTrabajador', 3)
WITH (Selected integer, integer Id_Tipo_Trabajador
,
Txt_Tipo_Trabajador varchar (30),
Porc_Incremento decimal (18.4),
Porc_Incremento_Despensa decimal (18.4) ,
Imp_Incremento_Despensa decimal (18.4),
Porc_Incremento_AyudaRenta decimal (18.4),
Imp_Incremento_AyudaRenta decimal (18,4))


Step 6: Make a select the temporary table to display information, eliminate temporary table to free up memory and finally eliminate the internal representation of the XML document through

sp_xml_removedocument
select * from # TTemporalTipoTrabajador DROP TABLE # TTemporalTipoTrabajador

sp_xml_removedocument EXEC @ DocHandle

Here Full Stored Procedure:




Hata then ... ..