March 20, 2003 at 1:09 pm
I was writing stored procedure to insert record/records in multiple tables in transaction mode. Sometimes I want to insert one record per table and sometimes it could be multiple records in any table. The records can have different values for fields for any table.
So what would be the good way to handle it at functionality level in programming or can I pass variable number of rows to stored procedure without increasing number of parameters.
March 20, 2003 at 8:59 pm
You can use OPENXML for adding multiple rows. It is extremely fast. I can post an example tomorrow if you wish.
Tim C.
//Will write code for food
Tim C //Will code for food
March 21, 2003 at 1:11 am
Another solution is to add the rows to be added to a temporary table first, then do a single INSERT INTO xyz SELECT * FROM #zyx to insert them at once into the real table.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 21, 2003 at 1:30 pm
chrhedga
How can I pass temporary table to stored procedure because I know that there are multiple rows at functional level?
tcartwright
are you talking that at functional level I will create an XML file and then I will openxml in stored procedure to insert.
March 21, 2003 at 1:43 pm
quote:
tcartwrightare you talking that at functional level I will create an XML file and then I will openxml in stored procedure to insert.
Yes, want me to post an example?
Tim C.
//Will write code for food
Edited by - tcartwright on 03/21/2003 1:44:03 PM
Tim C //Will code for food
March 21, 2003 at 2:20 pm
tcartwright!!
what do you mean by will write code for food. Is it just slogan?
If slogan then do post an example. I would appreciate your help.
March 21, 2003 at 3:04 pm
Will post it in a sec. Searching for it right now.
Tim C.
//Will write code for food
Tim C //Will code for food
March 21, 2003 at 3:47 pm
It's a joke, well not really anymore with the economy the way it is. If anyone
sees a 6'3 300lb man out on the side of the freeway holding a card board sign
with that slogan up in Houston, PLEASE give him a job. Anyway here is an
example I wrote about 6 months ago:
IMPORTANT NOTE - Please ensure that the DB you run this on does not have an EmployeeTestola
table.
Table creation and test data addition
DROP TABLE EmployeeTestola
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EmployeeTestola')
BEGIN
CREATE TABLE EmployeeTestola (
eid int IDENTITY (1, 1) NOT NULL ,
fname varchar (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
lname varchar (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
DECLARE @cntr int
SET @cntr = 1
SET NOCOUNT ON
WHILE @cntr <= 5
BEGIN
INSERT INTO EmployeeTestola VALUES ('FName' + CAST(@cntr as varchar), 'LName' + CAST(@cntr as varchar))
SET @cntr = @cntr + 1
END
SET NOCOUNT OFF
PRINT 'Newly created table data :'
PRINT ''
SELECT * FROM EmployeeTestola --FOR XML AUTO, XMLDATA
RETURN
END
This stored proc does all the work in one :UPDATE / INSERT / DELETE
It uses a virtual "IsDeleted bit flag that the client sets when they wish to
delete records.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC spUpdateEmployee (@empdata text)
AS
DECLARE @hDoc int
BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc OUTPUT, @empdata, '<root xmlns:x="x-schema:#Schema1"/>'
/*
***BEGIN DELETE BLOCK***
*/
DELETE FROM EmployeeTestola WITH (ROWLOCK)
WHERE EmployeeTestola.eid IN
(
SELECT eid
FROM OPENXML(@hDoc, '/root/x:EmployeeTestola[@eid > 0 and @IsDeleted = 1]', 1)
WITH
(
eid int '@eid'
)
)
/*
***END DELETE BLOCK**
*/
/*
***BEGIN UPDATE BLOCK***
*/
UPDATE EmployeeTestola WITH(ROWLOCK) SET
EmployeeTestola.fname = XMLEmployee.fname,
EmployeeTestola.lname = XMLEmployee.lname
FROM
(
SELECT eid, fname, lname
FROM OPENXML(@hDoc, '/root/x:EmployeeTestola[@eid > 0 and @eid != "" and @IsDeleted = 0]', 1)
WITH
(
eid int '@eid',
fname varchar(20) '@fname',
lname varchar(20) '@lname'
)
)XMLEmployee INNER JOIN EmployeeTestola
ON XMLEmployee.eid = EmployeeTestola.eid
/*
***END UPDATE BLOCK**
*/
/*
***BEGIN INSERT BLOCK***
*/
INSERT INTO EmployeeTestola WITH (ROWLOCK)
SELECT fname, lname
FROM OPENXML(@hDoc, '/root/x:EmployeeTestola[@eid <= 0 or @eid = "" and @IsDeleted = 0]', 1)
WITH
(
fname varchar(20) '@fname',
lname varchar(20) '@lname'
)
/*
***END INSERT BLOCK**
*/
EXEC sp_xml_removedocument @hDoc
IF @@ERROR = 0
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Testing code:
DECLARE @doc varchar(8000)
SET @doc =
'<root>
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="employeetestola" content="textOnly" model="closed">
<AttributeType name="eid" dt:type="i4"/>
<AttributeType name="fname" dt:type="string"/>
<AttributeType name="lname" dt:type="string"/>
<AttributeType name="IsDeleted" dt:type="boolean"/>
<attribute type="eid"/>
<attribute type="fname"/>
<attribute type="lname"/>
<attribute type="IsDeleted"/>
</ElementType>
</Schema>
<EmployeeTestola xmlns="x-schema:#Schema1" eid="1" fname="Joe" lname="Schmoe" IsDeleted="0"/>
<EmployeeTestola xmlns="x-schema:#Schema1" eid="2" fname="Jane" lname="Doe" IsDeleted="0"/>
<EmployeeTestola xmlns="x-schema:#Schema1" eid="3" fname="Abraham" lname="Lincoln" IsDeleted="1"/>
<EmployeeTestola xmlns="x-schema:#Schema1" eid="4" fname="Sam" lname="Adams" IsDeleted="0"/>
<EmployeeTestola xmlns="x-schema:#Schema1" eid="5" fname="George" lname="Washington" IsDeleted="0"/>
</root>'
EXEC spUpdateEmployee @doc
GO
SELECT * FROM EmployeeTestola --FOR XML AUTO, XMLDATA
Tim C.
//Will write code for food
Tim C //Will code for food
March 21, 2003 at 3:59 pm
Thanks tcartwright. You are great!!
You would be easily visible that I am sure and getting jobs would not be a problem for you.
March 22, 2003 at 2:03 am
quote:
How can I pass temporary table to stored procedure because I know that there are multiple rows at functional level?
Several possibilities for that; run dynamic query with temp table name in variable, use a 'real' table that is just used as a temp table and let some session id or something define the rows as owned by this connection.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply