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)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).
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)
--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)
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.