|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Update Stored Procedured questionCan I put two updates withing an update strored procedure? Is my syntax correct? Why I want to do this: My datatable is a union of two tables. It currently works well except for the fact that I have to update the records of corresponding tables separately. All records that came from table1 must use update stored procedure1. All records that came from table2 must use update stored procedure2. This adds complexity to the application by having to restrict users from updating records from one source or the other. One solution is to make a 'compound' update like this. I want to know if the syntax is correct. Specifically ~ putting both updates within the semicolons and both selects after the semicolons. Query Analyzer parses it successfully, but I don't know if it checks that thoroughly. BOL does not allow a search on ";" so that I can understand the syntax. Here is the update SQL \\ CREATE PROCEDURE dbo.usp_Union1To050Oper_upd( @pkID_u1 char(36), @fkJob_u1 char(36), @cmptDescription_u1 varchar(100), @Action_u1 varchar(50), @Duration_u1 numeric(5,3), @Order_u1 numeric(5,3), @Precedent_u1 numeric(5,3), @Delay_u1 numeric(5,3), @IOAddress_u1 varchar(50), @Note_u1 varchar(255), @CreatedOn_u1 smalldatetime, @CreatedBy_u1 varchar(25), @EditedOn_u1 smalldatetime, @EditedBy_u1 varchar(25), @Original_pkID_u1 char(36), @Original_rowversion_u1 timestamp ) AS SET NOCOUNT OFF; -- First Update 050 UPDATE tbl050Oper SET pkOperId = @pkID_u1, sqfkJob = @fkJob_u1, sqComponentDescription = @cmptDescription_u1, sqAction = @Action_u1, sqDuration = @Duration_u1, sqSooOrder = @Order_u1, sqSooPrecedent = @Precedent_u1, sqSooDelay = @Delay_u1, sqIOAddress = @IOAddress_u1, sqSooNote = @Note_u1, sqCreatedOn = @CreatedOn_u1, sqCreatedBy = @CreatedBy_u1, sqEditedOn = @EditedOn_u1, sqEditedBy = @EditedBy_u1 WHERE (pkOperId = @Original_pkID_u1) AND (sqrowversion = @Original_rowversion_u1) and tbl_u1 = 0 -- -- Second Update 040 UPDATE tbl040Cmpt SET pkComponentId = @pkID_u1, -- fkDevice = @fkJob_u1, ComponentDescription = @cmptDescription_u1, cmVarchar04 = @Action_u1, cmNumeric05 = @Duration_u1, SooOrder = @Order_u1, SooPrecedent = @Precedent_u1, SooDelay = @Delay_u1, cmVarchar01 = @IOAddress_u1,-- SooNote = @Note_u1, cmCreatedOn = @CreatedOn_u1, cmCreatedBy = @CreatedBy_u1, cmEditedOn = @EditedOn_u1, cmEditedBy = @EditedBy_u1 WHERE (pkComponentId = @Original_pkID_u1) AND (cmrowversion = @Original_rowversion_u1) and tbl_u1 = 1 ; -- First Select 050 select pkOperid as pkID_u1, sqfkJob as fkJob_u1, 0 as tbl_u1, sqComponentDescription as cmptDescription_u1, sqAction as Action_u1, sqDuration as Duration_u1, sqSooOrder as Order_u1, sqSooPrecedent as Precedent_u1, sqSooDelay as Delay_u1, sqIOAddress as IOAddress_u1, sqSooNote as Note_u1, sqCreatedOn as CreatedOn_u1, sqCreatedBy as CreatedBy_u1, sqEditedOn as EditedOn_u1, sqEditedBy as EditedBy_u1, sqrowversion as rowversion_u1 from tbl050Oper WHERE (pkOperId = @Original_pkID_u1) -- Second Select 040 SELECT pkComponentId as pkID_u1, fkDevice as fkJob_u1, 1 as tbl_u1, ComponentDescription as cmptDescription_u1, cmVarchar04 as Action_u1, cmNumeric05 as Duration_u1, sooOrder as Order_u1, sooPrecedent as Precedent_u1, sooDelay as Delay_u1, cmVarchar01 as IOAddress_u1, sooNote as Note_u1, cmCreatedOn as CreatedOn_u1, cmCreatedBy as CreatedBy_u1, cmEditedOn as EditedOn_u1, cmEditedBy as EditedBy_u1, cmrowversion as rowversion_u1 FROM tbl040cmpt WHERE (pkComponentId = @Original_pkID_u1) GO // > Can I put two updates withing an update strored procedure? Yes, you can do almost anything inside a stored procedure.> Is my syntax correct? Yes, but it doesn't do what (I think) you want it to do.The first UPDATE is totally independent from the second UPDATE which in turn is independent on the first SELECT which again is independent on the second SELECT. Semi-colon is essentially a dummy-sign in TSQL, there only because ANSI SQL say that a statement is to be ended by semi-colon. TSQL never required it (with exception for a few new language elements in 2005). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "dbuchanan" <dbuchana***@hotmail.com> wrote in message news:1134749089.150157.54610@o13g2000cwo.googlegroups.com... > Hello, > > Can I put two updates withing an update strored procedure? > > Is my syntax correct? > > Why I want to do this: > My datatable is a union of two tables. It currently works well except > for the fact that I have to update the records of corresponding tables > separately. > All records that came from table1 must use update stored procedure1. > All records that came from table2 must use update stored procedure2. > > This adds complexity to the application by having to restrict users > from updating records from one source or the other. > > One solution is to make a 'compound' update like this. > I want to know if the syntax is correct. Specifically ~ putting both > updates within the semicolons and both selects after the semicolons. > > Query Analyzer parses it successfully, but I don't know if it checks > that thoroughly. BOL does not allow a search on ";" so that I can > understand the syntax. > > Here is the update SQL > \\ > CREATE PROCEDURE dbo.usp_Union1To050Oper_upd( > @pkID_u1 char(36), > @fkJob_u1 char(36), > @cmptDescription_u1 varchar(100), > @Action_u1 varchar(50), > @Duration_u1 numeric(5,3), > @Order_u1 numeric(5,3), > @Precedent_u1 numeric(5,3), > @Delay_u1 numeric(5,3), > @IOAddress_u1 varchar(50), > @Note_u1 varchar(255), > @CreatedOn_u1 smalldatetime, > @CreatedBy_u1 varchar(25), > @EditedOn_u1 smalldatetime, > @EditedBy_u1 varchar(25), > > @Original_pkID_u1 char(36), > @Original_rowversion_u1 timestamp > ) AS > SET NOCOUNT OFF; > > -- First Update 050 > > UPDATE tbl050Oper SET > pkOperId = @pkID_u1, > sqfkJob = @fkJob_u1, > sqComponentDescription = @cmptDescription_u1, > sqAction = @Action_u1, > sqDuration = @Duration_u1, > sqSooOrder = @Order_u1, > sqSooPrecedent = @Precedent_u1, > sqSooDelay = @Delay_u1, > sqIOAddress = @IOAddress_u1, > sqSooNote = @Note_u1, > sqCreatedOn = @CreatedOn_u1, > sqCreatedBy = @CreatedBy_u1, > sqEditedOn = @EditedOn_u1, > sqEditedBy = @EditedBy_u1 > WHERE > (pkOperId = @Original_pkID_u1) > AND (sqrowversion = @Original_rowversion_u1) > and tbl_u1 = 0 -- > > -- Second Update 040 > > UPDATE tbl040Cmpt SET > pkComponentId = @pkID_u1, > -- fkDevice = @fkJob_u1, > ComponentDescription = @cmptDescription_u1, > cmVarchar04 = @Action_u1, > cmNumeric05 = @Duration_u1, > SooOrder = @Order_u1, > SooPrecedent = @Precedent_u1, > SooDelay = @Delay_u1, > cmVarchar01 = @IOAddress_u1,-- > SooNote = @Note_u1, > cmCreatedOn = @CreatedOn_u1, > cmCreatedBy = @CreatedBy_u1, > cmEditedOn = @EditedOn_u1, > cmEditedBy = @EditedBy_u1 > WHERE > (pkComponentId = @Original_pkID_u1) > AND (cmrowversion = @Original_rowversion_u1) > and tbl_u1 = 1 > ; > > -- First Select 050 > select > pkOperid as pkID_u1, > sqfkJob as fkJob_u1, > 0 as tbl_u1, > sqComponentDescription as cmptDescription_u1, > sqAction as Action_u1, > sqDuration as Duration_u1, > sqSooOrder as Order_u1, > sqSooPrecedent as Precedent_u1, > sqSooDelay as Delay_u1, > sqIOAddress as IOAddress_u1, > sqSooNote as Note_u1, > sqCreatedOn as CreatedOn_u1, > sqCreatedBy as CreatedBy_u1, > sqEditedOn as EditedOn_u1, > sqEditedBy as EditedBy_u1, > sqrowversion as rowversion_u1 > > from tbl050Oper > > WHERE > (pkOperId = @Original_pkID_u1) > > -- Second Select 040 > > SELECT > pkComponentId as pkID_u1, > fkDevice as fkJob_u1, > 1 as tbl_u1, > ComponentDescription as cmptDescription_u1, > cmVarchar04 as Action_u1, > cmNumeric05 as Duration_u1, > sooOrder as Order_u1, > sooPrecedent as Precedent_u1, > sooDelay as Delay_u1, > cmVarchar01 as IOAddress_u1, > sooNote as Note_u1, > cmCreatedOn as CreatedOn_u1, > cmCreatedBy as CreatedBy_u1, > cmEditedOn as EditedOn_u1, > cmEditedBy as EditedBy_u1, > cmrowversion as rowversion_u1 > > FROM tbl040cmpt > > WHERE > (pkComponentId = @Original_pkID_u1) > GO > // > Tibor,
> Yes, but it doesn't do what (I think) you want it to do. You might be right, but I don't think so. (?)If you don't mind, would you follow my logic and see if you *still* think so. The two updates are independant only in that they update different tables. Did you note the mapping? Here is the "lifetime" of one of the fields in the Update stored procdure; In the parameter block; @pkID_u1 char(36) In the first update; pkOperId = @pkID_u1 In the second update; pkComponentId = @pkID_u1 In the first select; pkOperid as pkID_u1 In the second select; pkComponentId as pkID_u1, Continuing to the Union select stored procdure (which is shown below) we have this; In the select; pkOperid as pkID_u1 In the union select; c.pkComponentId as pkID_u1, For your reference here is the Union select query; \\ CREATE PROCEDURE usp_Union1_sel (@fkJob char(36)) AS SET NOCOUNT ON; /** Union select gets 1.) Order of Operations ~ "tbl050Oper" 2.) Sequence of Operations ~ "tbl040cmpt" Filtered by fkJob **/ select pkOperid as pkID_u1, sqfkJob as fkJob_u1, 0 as tbl_u1, sqComponentDescription as cmptDescription_u1, sqAction as Action_u1, sqDuration as Duration_u1, sqSooOrder as Order_u1, sqSooPrecedent as Precedent_u1, sqSooDelay as Delay_u1, sqIOAddress as IOAddress_u1, sqSooNote as Note_u1, sqCreatedOn as CreatedOn_u1, sqCreatedBy as CreatedBy_u1, sqEditedOn as EditedOn_u1, sqEditedBy as EditedBy_u1, sqrowversion as rowversion_u1 from tbl050Oper WHERE -- (sqfkJob = @fkJob) sqfkJob = '18713191-e116-4d92-8cc2-c2d4e8c34fdb' union SELECT c.pkComponentId as pkID_u1, j.pkJobid as fkJob_u1, 1 as tbl_u1, c.ComponentDescription as cmptDescription_u1, c.cmVarchar04 as Action_u1, c.cmNumeric05 as Duration_u1, c.sooOrder as Order_u1, c.sooPrecedent as Precedent_u1, c.sooDelay as Delay_u1, c.cmVarchar01 as IOAddress_u1, c.sooNote as Note_u1, c.cmCreatedOn as CreatedOn_u1, c.cmCreatedBy as CreatedBy_u1, c.cmEditedOn as EditedOn_u1, c.cmEditedBy as EditedBy_u1, c.cmrowversion as rowversion_u1 FROM tbl040cmpt c -- job > proc > devi > cmpt -- j > p > d > c INNER JOIN tbl030Devi d ON c.fkDevice = d.pkDeviceId INNER JOIN tbl020Proc p ON d.fkProcess = p.pkProcessId INNER JOIN tbl010Job j ON p.fkJob = j.pkJobId -- INNER JOIN lkp201DeviceType dt ON p.fkDeviceType = dt.pkDeviceTypeId WHERE -- (j.pkJobid = @fkJob) j.pkJobid = '18713191-e116-4d92-8cc2-c2d4e8c34fdb' And fkComponentType <> 2 And fkComponentType <> 4 order by sqsooorder, sqComponentDescription GO // I bring two different tables together in a dataTable. To distinguish which records come from which table I assign "0" to column "tbl_u1" for one soucre and "1" to the same column for records that come from the other table. In the dataTable there nothing that gives a clue that it is made up of two very different tables. The union select stored procedure maps field names to combine common fields for a temporary purpose. Then for update the fields must be mapped back to distribute appropriate record updates to the porper tables. The 'compound' update stored procedure you looked at earlier is supposed to work like this; The parameter block takes values from the dataTable and assigns them to parameters The first update block updates the first table using appropriate mapping and includes this criteria in the where (tbl_u1 = 0) The second update block updates the second table using appropriate mapping and includes this criteria in the where (tbl_u1=1) The select statements are meant to update the dataTable with the changes. Am I missing anyting? Thank you, dbuchanan I might have been misguided by your comment about the semi-colon, thinking that you assumed that
there was any type of connection between the SQL statements in the procedures. I understand that the statements in the procedure can be connected in the sense that they use a common value for a WHERE clause (etc). Assuming that the tables has a column called tbl_u1, I can imagine that the logic can be OK (I haven't read through it thoroughly). Perhaps that column isn't needed because of the primary key, if they are distinct? OTOH, if the proc is called once per row (I don't know how a data adapter work), you can use an IF clause and then only update one of the tables for each call. But be aware that the optimizer optimizes selectivity based on the values passed into the proc, so whichever method you use you want to check the execution plans for each combination: Plan created by modifying table1, then fire the proc modifying table 1 and also fire the proc modifying table 2. And vice versa. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "dbuchanan" <dbuchana***@hotmail.com> wrote in message news:1134754407.721113.86550@g14g2000cwa.googlegroups.com... > Tibor, > >> Yes, but it doesn't do what (I think) you want it to do. > > You might be right, but I don't think so. (?) > > If you don't mind, would you follow my logic and see if you *still* > think so. > > The two updates are independant only in that they update different > tables. Did you note the mapping? > > Here is the "lifetime" of one of the fields in the Update stored > procdure; > > In the parameter block; @pkID_u1 char(36) > In the first update; pkOperId = @pkID_u1 > In the second update; pkComponentId = @pkID_u1 > In the first select; pkOperid as pkID_u1 > In the second select; pkComponentId as pkID_u1, > > Continuing to the Union select stored procdure (which is shown below) > we have this; > > In the select; pkOperid as pkID_u1 > In the union select; c.pkComponentId as pkID_u1, > > > For your reference here is the Union select query; > \\ > CREATE PROCEDURE usp_Union1_sel > (@fkJob char(36)) > AS SET NOCOUNT ON; > > /** > Union select gets > 1.) Order of Operations ~ "tbl050Oper" > 2.) Sequence of Operations ~ "tbl040cmpt" > > Filtered by fkJob > **/ > > select > > pkOperid as pkID_u1, > sqfkJob as fkJob_u1, > 0 as tbl_u1, > sqComponentDescription as cmptDescription_u1, > sqAction as Action_u1, > sqDuration as Duration_u1, > sqSooOrder as Order_u1, > sqSooPrecedent as Precedent_u1, > sqSooDelay as Delay_u1, > sqIOAddress as IOAddress_u1, > sqSooNote as Note_u1, > sqCreatedOn as CreatedOn_u1, > sqCreatedBy as CreatedBy_u1, > sqEditedOn as EditedOn_u1, > sqEditedBy as EditedBy_u1, > sqrowversion as rowversion_u1 > > from tbl050Oper > > WHERE > -- (sqfkJob = @fkJob) > sqfkJob = '18713191-e116-4d92-8cc2-c2d4e8c34fdb' > > union SELECT > c.pkComponentId as pkID_u1, > j.pkJobid as fkJob_u1, > 1 as tbl_u1, > c.ComponentDescription as cmptDescription_u1, > c.cmVarchar04 as Action_u1, > c.cmNumeric05 as Duration_u1, > c.sooOrder as Order_u1, > c.sooPrecedent as Precedent_u1, > c.sooDelay as Delay_u1, > c.cmVarchar01 as IOAddress_u1, > c.sooNote as Note_u1, > c.cmCreatedOn as CreatedOn_u1, > c.cmCreatedBy as CreatedBy_u1, > c.cmEditedOn as EditedOn_u1, > c.cmEditedBy as EditedBy_u1, > c.cmrowversion as rowversion_u1 > > FROM tbl040cmpt c > -- job > proc > devi > cmpt > -- j > p > d > c > INNER JOIN tbl030Devi d ON c.fkDevice = d.pkDeviceId > INNER JOIN tbl020Proc p ON d.fkProcess = p.pkProcessId > INNER JOIN tbl010Job j ON p.fkJob = j.pkJobId > -- INNER JOIN lkp201DeviceType dt ON p.fkDeviceType = dt.pkDeviceTypeId > > WHERE > -- (j.pkJobid = @fkJob) > j.pkJobid = '18713191-e116-4d92-8cc2-c2d4e8c34fdb' > And fkComponentType <> 2 > And fkComponentType <> 4 > > order by sqsooorder, sqComponentDescription > > GO > // > > > I bring two different tables together in a dataTable. To distinguish > which records come from which table I assign "0" to column "tbl_u1" for > one soucre and "1" to the same column for records that come from the > other table. > > In the dataTable there nothing that gives a clue that it is made up of > two very different tables. The union select stored procedure maps field > names to combine common fields for a temporary purpose. > > Then for update the fields must be mapped back to distribute > appropriate record updates to the porper tables. > > The 'compound' update stored procedure you looked at earlier is > supposed to work like this; > > The parameter block takes values from the dataTable and assigns them to > parameters > The first update block updates the first table using appropriate > mapping and includes this criteria in the where (tbl_u1 = 0) > The second update block updates the second table using appropriate > mapping and includes this criteria in the where (tbl_u1=1) > The select statements are meant to update the dataTable with the > changes. > > Am I missing anyting? > > Thank you, > dbuchanan > Tibor,
Thank you so much for your comments. They give me more to go on in making sure I am proceeding correctly. dbuchanan  |
|||||||||||||||||||||||
Other interesting topics