Can you call a query from inside another query?

Question

Currently I have a stored procedure that does some insert statements, calls another stored procedure which inserts something else and returns an id and then does some update statements. So a bit like this:

BEGIN
    SET NO COUNT ON;

    BEGIN TRY
        BEGIN TRAN

        -- Do some inserts

        DECLARE @MyIdCount int = (SELECT COUNT(MyId) FROM MappingTable)
        DECLARE @newId int

        WHILE @MyIdCount > 0
        BEGIN
           EXEC mySproc @parentId = @SomeId, @outId = @newId OUTPUT

           -- update table with result from stored procedure executed above
           @MyIdCount = @MyIdCount - 1
        END

        -- Do some updates based on the table that's been updated in the while loop

        SELECT * FROM MyUpdatedTable

        END TRAN
    END TRY
    BEGIN CATCH
        -- do something error
    END CATCH
END

I'd like to move this to use dapper as well as the stored procedure that's called within the WHILE loop, but can't see how I'll then be able to call that as part of this stored procedure.

Here's the stored procedure in the while loop:

BEGIN
    SET NOCOUNT ON;

    INSERT INTO Mapping(ParentId) 
    VALUES(@parentId)

    SELECT @outId = SCOPE_IDENTITY()
END

@outId is defined as an OUTPUT parameter and works fine when moved to dapper by accessing this:

var parameters = new DynamicParameters();
parameters.Add("@parentId", parentId);
parameters.Add("@outId", dbType: DbType.Int64, direction: ParameterDirection.Output);

// some query logic etc.

return parameters.Get<long>("@outId");  

UPDATE

So I think I should be able to do the following:

using (IDbConnection = Connection)
{
    var transaction = db.BeginTransaction();

    var inputDataParams = new DynamicParameters();
    //add various parameters for the inserts.

    db.Execute(New Input Data query, inputDataParams, transaction);

    var getOldIdsParams = new DynamicParameters():
    //add parameters for gettings old ids for mapping

    var oldIds = db.Query<long>(new query to get ids, getOldIdsParams, transaction).ToList();

    var mapping = new Dictionary<long, long>();

    var createNewIdsParams = new DynamicParameters();
    createNewidsparams.Add("@parentId", parentId);
    createNewidsparams.Add("@outId", , dbType: DbType.Int64, direction: ParameterDirection.Output);

    long newId;

    foreach(var oldId in oldIds)
    {
         db.Execute(Create Id Query, createNewIdsParams, transaction);
         newId = createNewidsparams.Get<long>("@outId");
         mapping.Add(oldId, newId);
    }

    var updateParameters = new DynamicParameters();
    // add params

    foreach(var oldId in oldIds)
    {
        updateParameters.Add(@oldId, oldId);
        updateParameters.Add(@newId, mapping[oldId]);
        db.Execute(New update query, updateParamters, transaction);
    }

    transaction.Commit();
}

I'll try and implement this in full and see if it works. If anyone can see something wrong with this please say! It will save me some time. Thanks

UPDATE 2

This appears to work.

Answer 1

Yes, you can use . A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Example1:

SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

Example2:

INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]

Please visit: https://www.tutorialspoint.com/sql/sql-sub-queries.htm