Selecting scalar values from a stored procedure

The easy and obvious answer is to use a function, not a procedure.   Then you can use it like any built-in T-SQL function.




select 1, dbo.Function()

But alas, functions do not allow us to store (insert, update, delete) any data to the database and my stored procedure needs to do exactly that. 



In a moment of brilliant engineering, SQL Server does not allow the return value of a stored procedure to appear as a column in a query (select, insert, update, where clause),  so you can't just say...




select orderID, dbo.StoredProcedure() from...

In my task, I had to insert the results of a stored procedure into a column in a table.  The code example I show below creates a temporary lookup table, and uses it later for a list of inserts.  try to follow this...




--creates a temp table for later use in joining (not shown)
declare @PickListNo table(OrderID varchar(20), PickListNo int, done bit);

--loads the temp table, except the column from the stored procedure
insert into @PickListNo (OrderID, done)
(
    Select distinct AOO.[order number], 0 from uploads.open_orders AOO
)

--vars the stored procedure needs
declare @A int
declare @NextVal int   

--while there are unprocessed rows...
while exists (select 1 from @PickListNo where PickListNo is null)
BEGIN
    --execute the procedure and capture the return value
    exec @A=dbo.NextPickListNo @NextVal OUTPUT
   
    --update one row in the table
    --I used Max(ID) to find a single row, but I might have used MIN, or Select top 1 as well.
    update @PickListNo set PicklistNo = @A where OrderID=(select MAX(orderID) from @PickListNo where PickListNo is null)
END

This use of the while loop is my way of cheating and not using cursors.  Cursors are nicely powerful but demand a lot of babysitting and resources (so I am told).



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.