Combine these 3 SQL steps into 1 query

Question

SQL Server 2017 (In Azure) - when I need to create a new client in our clients database, I have to run three separate queries, and in between each query, do a lookup to be able to populate a part of the next query. I'd like to see if there is a way to combine all this into one query, or, parameterized stored procedure:

All of this takes place in the same database called Clients:

Step 1 - Create the client record in dbo.clients:

INSERT INTO dbo.clients
(ClientGuid, Name, Permissions)
VALUES 
(NEWID(), 'Contoso', 1)

Step 2 - Get the Primary Key which was auto-created in Step 1:

SELECT ClientKey from dbo.clients
WHERE Name = 'Contoso'

Now write down the primary key (ClientKey) from that record, we'll say 12345678

Step 3 - Create a new billing code in the dbo.billingcodes table:

INSERT INTO dbo.billingcodes
(BillingCodeGuid, ClientKey, Name, ScoreId)
VALUES
(NEWID(), 12345678, 'Contoso Production Billing Code', 1)

How can I combine all this into one query or parameterized stored procedure where all I have to enter in are the two names from step 1 and 3 (assume the Permissions and ScoreId integers are always going to be 1) and also get an output at the end of the process of the created values for dbo.clients.ClientKey and dbo.billingcodes.BillingCodeGuid?

Clever. The issue I'm having is trying to understand how to get values without having to manually look them up and copy/paste them into a new, separate query. So I AM stuck. — Stpete111, Aug 08 '19 at 23:11
Possible duplicate of [Best way to get identity of inserted row?](https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Dale K, Aug 08 '19 at 23:22
That thread speaks only to Identity values and does not address getting the value created by `NEWID()`. I found helpful information here which may guide me: https://stackoverflow.com/questions/1509947/scope-identity-for-guids Once I formulate the stored procedure and it works correctly, I will post it here as the answer. — Stpete111, Aug 08 '19 at 23:31
But in your example you're obtaining the ClientKey, which you say is 12345678? So thats clearly a int you are trying to obtain? In fact from the thread I posted the `OUTPUT clause` works for both and is probably the most versatile solution. But of course you can always create a new guid before you insert it, for a single record, but thats trivial. — Dale K, Aug 09 '19 at 00:38

score 1 · Answer 1 · answered Aug 08 '19 at 23:13

1

You could create a procedure that consists of both inserts with a line in between to get the ID of the inserted client. Assign the ID to a variable and pass it in to the second part.

See this post about some different ways about getting the inserted record’s ID Best way to get identity of inserted row?

answered Aug 08 '19 at 23:13

Joe LaRue

132
1
5

Makes sense, Joe, thanks for this answer and being willing to assist - I'll look into this. – Stpete111 Aug 08 '19 at 23:21

score 1 · Answer 2 · answered Aug 09 '19 at 04:44

You could do it by using procedure. You may find this link for creating procedure in SQL Server Link.

In case of Procedure , need to insert your data into first table. Then using IDENT_CURRENT (Ident_Current) you'll get your last inserted id from table, which will further use to insert it into next table.

Combine these 3 SQL steps into 1 query

2 Answers2