Stored Procedure with Output
-
I know this is a newbie question but I am a newbie to writing stored procedures, Ok I want to right a stored procedure that copys data from one table to another table based on a certain ID then output back to the program that executes the stored procedure the ID of the next record available in the table that was just copied into. So basically i have table x and y, y is a history table of x, whenever things are about to change in x i save certain records (based on an ID) into Y, after I save this specific data to y, I want to ouput Y next record ID that is avaible(ex. if I add rows with IDs 1-10, i want to output back either 11). So how can I do this? Any Help will be greatly appreciated.
-
I know this is a newbie question but I am a newbie to writing stored procedures, Ok I want to right a stored procedure that copys data from one table to another table based on a certain ID then output back to the program that executes the stored procedure the ID of the next record available in the table that was just copied into. So basically i have table x and y, y is a history table of x, whenever things are about to change in x i save certain records (based on an ID) into Y, after I save this specific data to y, I want to ouput Y next record ID that is avaible(ex. if I add rows with IDs 1-10, i want to output back either 11). So how can I do this? Any Help will be greatly appreciated.
Insert Into TableA ( ColumnList ) Select SimiliarColumnLIst From TableB Where Conditional After your insert statement, add either of the following SELECT @@IDENTITY OR SELECT SCOPE_IDENTITY() @@IDENTITY will return the ID of the last record inserted. If you have triggers on your table(s), then it would be the ID of the last record inserted by a trigger, in which case SCOPE_IDENTITY() returns the ID of the newest record, even if a trigger were to perform inserts against other tables. I personally always use SCOPE_IDENTITY() since I know what it will return now and in teh future, regardless of changes to the DB. - Sage LinkRenter