For Simplicity;
I have two tables in a database, say Customer table and Orders table.
The customer table has two columns.
i) Customer ID (int)(Primary key)
ii)Name(varchar(100))
The orders table has two columns.
i)Orders ID(int) which is a foreign key to the primary key mentioned above.
ii)Sale Price (int)
As we know in SQL Server 2005 edition, the Primary key Identity is auto generated when we add an entry to the Customer Table.
I want to write a stored procedure which takes only the inputs name and sale price from the user(say from two textboxes) and should be stored in the above tables in CustomerTable.Name and OrderTable.SalePrice respectively.
I want to get the primary key from the Customer table, as soon as it is generated and use the value to store the salePrice in the next table.
I can gat the value(primary key) from comparing with the name the user passed in, but that would fail if there are more than one user with same name. Instead I wish to get the value of the primary key to a temporary variable as soon as it is generated and use it to fill the foreign key of the Orders TAble.
Can anyone help me ??
Thanks!
