Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
overbored
Starting Member
12 Posts |
 Posted - 2002-07-14 : 13:33:25
|
| I'm new to SQL, and I'm wondering how to solve this problem (simplified from actual application I'm working on). Let's say I have two tables: Authors and Books. Authors has fields:- a_id- a_nameand Books has fields:- b_id- b_title- a_idHow would I write this stored procedure? It accepts an author name and a book title. If the author already exists, it will simply insert the new book record with the existing author record's ID. Otherwise, it will insert a new author and use this new ID for the new book record.Any help would be appreciated, thanks a lot. |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-07-14 : 14:04:51
|
| You could have a stored procedure that accepts all the parameters. Inside the stored procedure, you could check to see if the author already exists in the authors table. For example:IF EXISTS( SELECT 'Y' FROM Authors WHERE a_name = 'Whatever')BEGIN INSERT INTO Books (b_id, b_title, a_id) SELECT @b_id_param, @b_title_param, (SELECT a_id FROM Authors WHERE a_name = @a_name_param)ENDELSEBEGIN INSERT INTO Authors (a_id, a_name) VALUES (Whatever) INSERT INTO Books (b_id, b_title, a_id) SELECT @b_id_param, @b_title_param, (SELECT a_id FROM Authors WHERE a_name = @a_name_param)END--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
|
|
|
|
|
INSERT but only when necessary?