|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sproc variable question-newbeHi,
Can I declare variables in sprocs that are to be used just in the sproc? The reason I need this is because I need to make 2 insert statements and I need to store the @@Identities of those inserts so I can make an insert into another table where the @@identities are foreign keys. Thanks Newbe Enterprise (gev***@gmail.com) writes:
> Can I declare variables in sprocs that are to be used just in the I may be misunderstanding what you are really looking for, but if I> sproc? > The reason I need this is because I need to make 2 insert statements > and I need to store the @@Identities of those inserts so I can make an > insert into another table where the @@identities are foreign keys. got it right this is what you looking for: CREATE PROCEDURE inserttbls @var1 int, @var2 int ... AS DECLARE @myident int INSERT firsttbl (...) VALUES(...) SELECT @myident = scope_identity INSERT nexttbl (fkcol, ...) VALUES (@myident, ...) Or are you insering many rows into the first table? Then curse the day you made that column an IDENTITY column! If you are on SQL 2005, you can use the OUTPUT clause of the INSERT statement - provided that the data includes a real key beside the IDENTITY column. If there isn't a real key, or if you are on SQL 2005, you should seriously consider changing the table to not use IDENTITY, unless you need it for scalability reasons. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> Can I declare variables in sprocs that are to be used just in the sproc? <<Variables that are declared in a procedure body are local to that module. But you have to remember that T-SQL is a simple one pass compiler and does not optimize code. The fewer local variables you can use, the faster and smaller your code -- you have to do it by hand, instead of depending on the compiler. >> The reason I need this is because I need to make 2 insert statementsand I need to store the @@Identities of those inserts so I can make an insert into another table where the @@identities are foreign keys. << Why don't you have a REAL, relational key? You are mimicing pointerchains. Look up the definition of a key -- it has to be attributes in the data model and not an exposed physical locator created by the internal state of the hardware. Thanks for the replies.
I have 3 tables that I need to update. Table1(pkTable1ID, fkTable2ID, fkTable3ID, ...) Table2(pkTable2ID, .....) Table3(pkTable3ID, ....). pk= primary key fk = foreign key. So in the database diagram pk<=>fk. So in order to add a record to table1 I have to insert a record into Table2 and Table3, then insert the keys of those new records into Table1. So I thought I'd store the @@Identity which according to documentation is the SQL server global variable for last records ID(identity) value. So I'd insert into table2, store ID in local variable of sproc, then update table3 and store ID in another local variable in the sproc, then .... update table1 with the values of the ID's stored in local variables. I might be going about this in the wrong way...please let me know if I am. I tried using views, but they told me that they were unupdatable since multiple tables need to be updated. So I thought why not have a form on the front end with all the information and an sproc that updates multiple tables...? THanks P.S. Using SQL server 2005 --CELKO-- wrote: Show quote > >> Can I declare variables in sprocs that are to be used just in the > sproc? << > > Variables that are declared in a procedure body are local to that > module. But you have to remember that T-SQL is a simple one pass > compiler and does not optimize code. The fewer local variables you can > use, the faster and smaller your code -- you have to do it by hand, > instead of depending on the compiler. > > >> The reason I need this is because I need to make 2 insert statementsand I need to store the @@Identities of those inserts so I can make an insert into another table where the @@identities are foreign keys. << > > Why don't you have a REAL, relational key? You are mimicing pointer > chains. Look up the definition of a key -- it has to be attributes in > the data model and not an exposed physical locator created by the > internal state of the hardware. Enterprise (gev***@gmail.com) writes:
Show quote > Thanks for the replies. Yes, as long as you are inserting one row this is simple:> I have 3 tables that I need to update. Table1(pkTable1ID, fkTable2ID, > fkTable3ID, ...) > Table2(pkTable2ID, .....) Table3(pkTable3ID, ....). > pk= primary key fk = foreign key. So in the database diagram pk<=>fk. > > So in order to add a record to table1 I have to insert a record into > Table2 and Table3, then insert the keys of those new records into > Table1. > So I thought I'd store the @@Identity which according to documentation > is the SQL server global variable for last records ID(identity) value. > So I'd insert into table2, store ID in local variable of sproc, then > update table3 and store ID in another local variable in the sproc, then > ... update table1 with the values of the ID's stored in local > variables. DECLARE @idtbl2 int, @idtbl3 int INSERT table2(...) VALUES (...) SELECT @idtbl2 = scope_identity() INSERT table3(...) VALUES (...) SELECT @idtbl3 = scope_identity() INSERT table1 (fkTable2ID, fkTable3ID) VALUES @idtbl2, @idtbl3) Note that I am not using @@identity, but scope_identity(). The difference between the two is that @@identity would give you the wrong value if any of tbe tables has a trigger that also inserts rows to a table with an IDENTITY column. scope_identity() is constrained to the current scope, that is your stored procedure. Therefore it's better to use scope_identity() in most situations. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you very much.
Erland Sommarskog wrote: Show quote > Enterprise (gev***@gmail.com) writes: > > Thanks for the replies. > > I have 3 tables that I need to update. Table1(pkTable1ID, fkTable2ID, > > fkTable3ID, ...) > > Table2(pkTable2ID, .....) Table3(pkTable3ID, ....). > > pk= primary key fk = foreign key. So in the database diagram pk<=>fk. > > > > So in order to add a record to table1 I have to insert a record into > > Table2 and Table3, then insert the keys of those new records into > > Table1. > > So I thought I'd store the @@Identity which according to documentation > > is the SQL server global variable for last records ID(identity) value. > > So I'd insert into table2, store ID in local variable of sproc, then > > update table3 and store ID in another local variable in the sproc, then > > ... update table1 with the values of the ID's stored in local > > variables. > > Yes, as long as you are inserting one row this is simple: > > DECLARE @idtbl2 int, > @idtbl3 int > > INSERT table2(...) VALUES (...) > SELECT @idtbl2 = scope_identity() > > INSERT table3(...) VALUES (...) > SELECT @idtbl3 = scope_identity() > > INSERT table1 (fkTable2ID, fkTable3ID) > VALUES @idtbl2, @idtbl3) > > Note that I am not using @@identity, but scope_identity(). The difference > between the two is that @@identity would give you the wrong value if > any of tbe tables has a trigger that also inserts rows to a table with > an IDENTITY column. scope_identity() is constrained to the current > scope, that is your stored procedure. Therefore it's better to use > scope_identity() in most situations. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
|
|||||||||||||||||||||||
