Stewart Alan Stewart Alan - 2 months ago 9
SQL Question

How do I return the result of a stored procedure to a select statement that is being used to insert records

I am writing a script to transform some data following a software update. I am introducing a new table and need to extract data from an existing table into this new table. I need to take an existing varchar(max) field from one table, create a parent child record based on it in another table and insert the identity of the new parent record in another table.

So it kinda want to look like this

INSERT INTO NEWTABLE (val1, val2, val3)
SELECT
val1,
val2,
EXEC AStoredProc(val3) -- this obviosuly doesnt work
FROM EXISTINGTABLE

CREATE PROCEDURE AStoredProc
@Value nvarchar(max),
@NewID int OUTPUT

DECLARE @ParentID int

INSERT INTO AnotherTable ([Text], ParentNodeID)
VALUES ('',NULL)

SELECT @ParentID = SCOPE_IDENTITY()

INSERT INTO AnotherTable ([Text], ParentNodeID)
VALUES (@Value,@ParentID)

SELECT @ParentID

END


In the above example val1 and val2 fields are just being migrated directly to the new table. val3 in the new table is an int and is being inserted from the result of AStoredProc which takes the varchar(max) val3 value of the Existing table creates a couple of records in another table and returns the new parent ID from them, which is then inserted as val3 in the new table.

Without worrying about the whys of the design, how can I essentially run some insert stuff in a Stored Proc and have the result returned directly to the outer Select statement?

Answer

The two options I can think of are:

  1. Use a CURSOR:

    DECLARE @val1 datatype1,
            @val2 datatype2,
            @val3 NVARCHAR(MAX),
            @ParentID INT;
    
    DECLARE migrate CURSOR LOCAL STATIC
    FOR  SELECT et.val1, et.val2, et.val3
         FROM   EXISTINGTABLE et
         WHERE  NOT EXISTS (
                       SELECT *
                       FROM   NEWTABLE nt
                       WHERE  nt.val1 = et.val1
                       AND    nt.val2 = et.val2
                           );
    
    BEGIN TRY
    
    OPEN migrate
    
    FETCH NEXT
    FROM  migrate
    INTO  @val1, @val2, @val3;
    
    WHILE (@@FETCH_STATUS <> 0)
    BEGIN
      BEGIN TRAN;
    
      EXEC dbo.AStoredProc
               @Value = @val3,
               @NewID = @ParentID OUTPUT;
    
      INSERT INTO NEWTABLE (val1, val2, val3)
      VALUES (@val1, @val2, @ParentID);
    
      COMMIT;
    END;
    
    CLOSE migrate;
    DEALLOCATE migrate;
    
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN;
      -- catch block stuff goes here
    END CATCH;
    
  2. Create a "temporary" column in at least the first AnotherTable listed in the stored procedure in the example code (i.e. the table generating the @ParentID value) to hold an association for the newly created records. This would allow for doing set-based operations since the queries could then be done as separate steps. If there is already an IDENTITY value / easy to use PK in EXISTINGTABLE then just use that, else a new value can be created easily if the temporary column is UNIQUEIDENTIFIER and just use NEWID(). But assuming happy path for the moment (i.e. EXISTINGTABLE has INT PK), then it would be something like:

    -- set up temporary association column
    ALTER TABLE dbo.AnotherTable
      ADD [TempAssociationID] INT NOT NULL;
    
    BEGIN TRY
    
    BEGIN TRAN;
    
    -- populate initial "parent" records
    INSERT INTO dbo.AnotherTable ([Text], [TempAssociationID])
      SELECT '', et.PK_column
      FROM   dbo.EXISTINGTABLE et;
    
    -- populate "child" records
    INSERT INTO dbo.AnotherTable ([Text], [TempAssociationID], ParentNodeID)
      SELECT et.val3, et.PK_column, anthr.ID_column AS [ParentNodeID]
      FROM   dbo.EXISTINGTABLE et
      INNER JOIN  dbo.AnotherTable anthr
              ON  anthr.[TempAssociationID] = et.PK_column
             AND  anthr.[ParentNodeID] IS NULL;
    
    -- combine existing and secondary tables for "new" table
    INSERT INTO dbo.NEWTABLE (val1, val2, val3)
      SELECT  et.val1, et.val2, anthr.ID_column AS [val3]
      FROM    dbo.EXISTINGTABLE et
      INNER JOIN  dbo.AnotherTable anthr
              ON  anthr.[TempAssociationID] = et.PK_column
             AND  anthr.[ParentNodeID] IS NULL;
    
    COMMIT TRAN;
    
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN;
      -- other catch block stuffs
    END CATCH;
    
    ALTER TABLE dbo.AnotherTable
      DROP COLUMN [TempAssociationID];
    
Comments