Cursor with multi "Exec sp_executesql @var1 , @var2" error ;-(

  • juliosousa90

    SSC Journeyman

    Points: 81

    Hello, I have a table with multi-row select statements, and I want to insert the results into a new table in another database.
    Example below:

    --- 4 data bases db1, db2 , db3 and dbmain

    CREATE DATABASE db1
    CREATE DATABASE db2
    CREATE DATABASE db3
    CREATE DATABASE dbmain
    GO

    Create table db1.dbo.companies(companiename nvarchar(100),creationdate date)
    Create table db2.dbo.companies(companiename nvarchar(100),creationdate date)
    Create table db3.dbo.companies(companiename nvarchar(100),creationdate date)
    GO

    INSERT INTO db1.dbo.companies(companiename, creationdate)
    VALUES (N'companie1', '20180215 00:00:00.000')
    GO

    INSERT INTO db2.dbo.companies(companiename, creationdate)
    VALUES (N'companie2', '20180216 00:00:00.000')
    GO

    INSERT INTO db3.dbo.companies(companiename, creationdate)
    VALUES (N'companie3', '20180217 00:00:00.000')
    GO

    SELECT
    'use '+name + ' select companiename from companies' as companiename,
    'use '+name + ' select creationdate from companies' as creationdate
    into dbmain.dbo.tmpcompaniesdbs
    FROM sys .databases
    where name like 'db1' or name like 'db2' or name like 'db3'
    ORDER BY create_date DESC
    GO

    Create table dbmain.dbo.companies(companiename nvarchar(100),creationdate date)
    GO

    DECLARE @var1 NVARCHAR(MAX);
    DECLARE @var2 NVARCHAR(MAX);
    DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT companiename ,creationdate
    FROM dbmain.dbo.tmpcompaniesdbs
    OPEN Cur
    FETCH NEXT FROM Cur INTO @var1, @var2

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
            INSERT INTO dbmain.dbo.companies(companiename,creationdate)
            Exec sp_executesql @var1,@var2
                  
       FETCH NEXT FROM Cur INTO @var1,@var2
    END
    CLOSE Cur
    DEALLOCATE Cur;
    GO

  • DesNorton

    SSC-Insane

    Points: 22597

    Firstly, there is no need for a cursor to do what you are trying to do.
    As a rule of thumb, cursors should be avoided.

    The following SQL will do the same without using a cursor.
    INSERT INTO dbmain.dbo.companies(companiename, creationdate)
    SELECT companiename, creationdate
    FROM dbmain.dbo.tmpcompaniesdbs;

    If you insist on using a cursor with Dynamic SQL ....

    Change the datat types of @var1 and @var2 to match the data types of the underlying data
    DECLARE @var1 NVARCHAR(100);
    DECLARE @var2 DATE;

    Then build the entire insert statement into the execstring, and pass in the values
    Exec sys.sp_executesql
      @stmt = N'INSERT INTO dbmain.dbo.companies(companiename,creationdate) values (@var1, @var2);'
    , @params = N'@var1 NVARCHAR(100) INT, @var2 DATE'
    , @var1 = @var1
    , @var2 = @var2;

  • juliosousa90

    SSC Journeyman

    Points: 81

    Hello,
    Thanks !!

    It was a fantastic help 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply