1

I've found hundreds of articles about how to insert output of stored procedure into a table but nobody goes a step further and explains how to insert additional data as column.

I'm looping through a table and execute store procedure which I insert details into tempTable however I need to add additional column with an ID from second table. I could change stored procedure to return that additional ID however I need to run it on 20+ databases on different servers which would be time consuming.

DECLARE @c_id_view varchar(255)
DECLARE @c_id int

DECLARE cur CURSOR FOR SELECT c_id FROM #temp where ins != 'Health'
OPEN cur

FETCH NEXT FROM cur INTO @c_id_view

WHILE @@FETCH_STATUS = 0 BEGIN
set @c_id = (select c_id from claim where c_id_view = @c_id_view)
insert into #tempbedsNONIL (c_id_view,room_Type,ward_name,common_bed_name,bed_name,no_of_bed,no_of_days) values (@c_id_view, usp_patient_transfer_retrieve_forPDF @c_id)  -- call your sp here
FETCH NEXT FROM cur INTO @c_id_view

END

CLOSE cur    
DEALLOCATE cur

I know that to insert SP results into a table I just need to do INSERT INTO #TEMP EXEC sp_test but in my case I need to enter extra value. This might be a stupid question but I can't figure out how to achieve it.

2

A possible option is to use the technique from this post.

Basically, you declare a table variable to hold the results of the stored procedure and then use an additional insert statement to add the extra column.

In the following example, 3 columns are returned from the sp_readerrorlog stored procedure and the additional insert adds servername.

Just make sure you truncate the table variable before each invocation of the stored procedure within your loop.

DECLARE @readerrorlog_results TABLE (
  LogDate date,
  ProcessInfo varchar(max),
  Text varchar(max)
)

INSERT INTO @readerrorlog_results
      (LogDate,ProcessInfo,Text)
EXEC sp_readerrorlog 0, 1, 'Login failed'

INSERT INTO FailedLogins
      (LogDate,  ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results
| improve this answer | |
0

Another approach is just to insert only the columns that are output from the stored procedure, and update those rows immediately afterward. I have no idea what your stored procedure does or why you can't create a version of this that handles the whole set at once instead of all this inefficient one-by-one processing, but let's say it does this (just outputting one column, to simplify):

CREATE PROCEDURE dbo.usp_patient_transfer_retrieve_forPDF
  @c_id int
AS
BEGIN
  SELECT room_type = 'c_' + name + '_' + CONVERT(varchar(11),@c_id)
    FROM sys.columns 
    WHERE [object_id] = @c_id;
END
GO

And let's say our temp table has one additional column:

CREATE TABLE #tempbedsNONIL
(
  c_id_view varchar(255),   -- from our variable
  room_type varchar(255)    -- from stored procedure output
);

As long as c_id_view is nullable in the table, we can just leave it out of the insert. And as long as the source (#temp) doesn't allow it to be NULL, after the insert we can just update the rows where that column is NULL.

DECLARE @c_id_view varchar(255), @c_id int;

DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
  SELECT TOP (10) name 
  FROM sys.objects
  WHERE name IS NOT NULL;

OPEN cur;

FETCH NEXT FROM cur INTO @c_id_view;
WHILE @@FETCH_STATUS <> -1
BEGIN

  -- why isn't this just a join and part of the cursor?
  SELECT @c_id = [object_id] FROM sys.objects WHERE name = @c_id_view;

  -- insert just the columns the procedure provides:
  INSERT #tempbedsNONIL(room_type) 
    EXEC dbo.usp_patient_transfer_retrieve_forPDF @c_id = @c_id;

  -- now, update the rows you just inserted:
  UPDATE #tempbedsNONIL SET c_id_view = @c_id_view WHERE c_id_view IS NULL;

  FETCH NEXT FROM cur INTO @c_id_view;
END

CLOSE cur;
DEALLOCATE cur;

Now, while that did take an additional operation on each iteration of the loop, you should see that they are all complete:

SELECT c_id_view, room_type FROM #tempbedsNONIL;

Some additional resources you may find useful:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.