Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Creating a cursor with a dynamic database name.

    You will either know this or you won't. I want to do thisthe following two lines of TSQL in one dynamically but none of the Declare Cursor statements work (when I try to pass in the Database name using a parameter). How do i dynamicically create a cursor to a table using a dynamic database/catalog name?

    DECLARE curTest1 CURSOR SELECT * FROM testDB1.dbo.MyTable
    DECLARE curTest2 CURSOR SELECT * FROM testDB2.dbo.MyTable

    I've tried the following

    DECLARE @CatalogName NVARCHAR(5)
    DECLARE @sqlStr NVARCHAR (4000)

    SET @CatalogName = 'TestDB'
    SET @sqlStr = 'SELECT * FROM ' + @CatalogName + 'dbo.Mytable;'

    DECLARE curTest CURSOR FOR SELECT * FROM @CatalogName.dbo.MyTable -- Which obviously should not and does not work.
    DECLARE curTest CURSOR FOR @sqlSTR -- Which I thought would work but also does not work.

    CLOSE curTest
    DEALLOCATE curTest

    My environment is SQL Server 2000 environment SP3

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    the answer you are looking for is sp_executesql.....

    you can't use a parameter value in the way you are trying...

    you would need to dynamicly create and execute your sql statement using sp_executesql.

    there have been a few posts in the last few days that explain this to the nth degree.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can't declare with dynamic sql...

    Code:
    DECLARE @declare varchar(2000)
    
    SET @declare = 'DECLARE @x int'
    
    sp_executesql(@declare)
    And why do you want to use a cursor?

    Think of dynamic sql being "outside" the scope of the current thread...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2002
    Posts
    192
    Actually you're both wrong. I figured it out.

    Turns out you need to use the EXEC command to execute the string.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes I am....and I wish I wasn't

    Why would you want to do this?

    You going to build the Fetches dynamically?

    How about the Declarations of the variables...That I don't think you can do indynamic sql

    but this (to my UTTER amazement)..wrks:

    Code:
    USE Northwind
    GO
    
    DECLARE @cmd varchar(8000), @ShippedDate datetime
     SELECT @cmd = 'DECLARE myCursor CURSOR FOR SELECT ShippedDate FROM Orders'
       EXEC(@Cmd)
    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @ShippedDate
    SELECT @ShippedDate
    CLOSE myCursor
    DEALLOCATE myCursor
    Good luck...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2002
    Posts
    192
    The short answer is consolidated reporting on Accounting systems.


    Most modern accounting systems allow for multiple companies to be managed from one server. To accomodate this, a seperate database is created for each company but fortunately the structure of the tables does not change between companyies . As a result, to report consolidated figures for the entire organization you want to have catalog names passed in dynamically especially if your oganization contains many companies.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the sproc?

    I'd like to see if there's a non cursor way....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2002
    Posts
    192
    Here is one of them.

    CREATE PROCEDURE sp_Sales_Summary_Update_02_03

    @CatalogName NVARCHAR(5),
    @ItemNumber NVARCHAR(31),
    @Warehouse NVARCHAR(11),
    @PurchaseTableName NVARCHAR(8),
    @PurchaseLineTableName NVARCHAR(8)
    AS

    DECLARE @FromTheYear INT
    DECLARE @FromTheWeek INT
    DECLARE @ToTheYear INT
    DECLARE @ToTheWeek INT
    DECLARE @QuantityOrdered NUMERIC(19,5)
    DECLARE @strCursorString NVARCHAR(4000)

    SET @strCursorString = ''
    SET @strCursorString = @strCursorString + 'DECLARE curQuantityOrdered CURSOR FORWARD_ONLY FOR '
    SET @strCursorString = @strCursorString + 'SELECT '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE) AS FromTheYear, '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE) AS FromTheWeek, '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PRMSHPDTE) AS ToTheYear, '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PRMSHPDTE) AS ToTheWeek, '
    SET @strCursorString = @strCursorString + ' ''' + @CatalogName + ''' AS CompanyID, '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.ITEMNMBR AS ItemNumber, '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.LOCNCODE AS Warehouse, '

    SET @strCursorString = @strCursorString + ' SUM(' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.QTYORDER) AS QuantityOrdered '
    SET @strCursorString = @strCursorString + 'FROM ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + ' LEFT OUTER JOIN ' + @CatalogName + '.dbo. ' + @PurchaseTableName + ' '
    SET @strCursorString = @strCursorString + ' ON ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PONUMBER = ' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.PONUMBER '
    SET @strCursorString = @strCursorString + 'WHERE '
    SET @strCursorString = @strCursorString + ' (' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.POSTATUS <> 6) AND '
    SET @strCursorString = @strCursorString + ' (' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.QTYORDER <> 0) '
    SET @strCursorString = @strCursorString + 'GROUP BY '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE), '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE), '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PRMSHPDTE), '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo. ' + @PurchaseLineTableName + '.PRMSHPDTE), '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.ITEMNMBR, '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.LOCNCODE '
    SET @strCursorString = @strCursorString + 'HAVING '
    SET @strCursorString = @strCursorString + ' (' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.ITEMNMBR = ''' + @ItemNumber + ''') AND '
    SET @strCursorString = @strCursorString + ' (''' + @CatalogName + ''' = ''' + @CatalogName + ''') AND '
    SET @strCursorString = @strCursorString + ' (' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.LOCNCODE = ''' + @Warehouse + ''') '
    SET @strCursorString = @strCursorString + 'ORDER BY '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE), '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo. ' + @PurchaseTableName + '.DOCDATE), '
    SET @strCursorString = @strCursorString + ' YEAR(' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PRMSHPDTE), '
    SET @strCursorString = @strCursorString + ' DATEPART(WEEK, ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.PRMSHPDTE), '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.ITEMNMBR, '
    SET @strCursorString = @strCursorString + ' ' + @CatalogName + '.dbo.' + @PurchaseLineTableName + '.LOCNCODE '
    PRINT @strCursorString
    EXECUTE(@strCursorString)

    OPEN curQuantityOrdered

    FETCH NEXT FROM curQuantityOrdered INTO @FromTheYear, @FromTheWeek, @ToTheYear, @ToTheWeek, @CatalogName, @ItemNumber, @Warehouse, @QuantityOrdered
    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE tblSalesSummary
    SET QuantityOrdered = @QuantityOrdered
    FROM
    tblSalesSummary
    WHERE
    (CompanyID = @CatalogName) AND
    (ItemNumber = @ItemNumber) AND
    (Warehouse = @Warehouse) AND
    CASE
    WHEN TheWeek < 10 THEN
    CAST(TheYear AS nvarchar(4)) + '0'+ CAST(TheWeek AS nvarchar(2))
    ELSE
    CAST(TheYear AS nvarchar(4)) + CAST(TheWeek AS nvarchar(2))
    END
    BETWEEN
    CASE
    WHEN @FromTheWeek < 10 THEN
    CAST(@FromTheYear AS nvarchar(4)) + '0'+ CAST(@FromTheWeek AS nvarchar(2))
    ELSE
    CAST(@FromTheYear AS nvarchar(4)) + CAST(@FromTheWeek AS nvarchar(2))
    END
    AND
    CASE
    WHEN @ToTheWeek < 10 THEN
    CAST(@ToTheYear AS nvarchar(4)) + '0'+ CAST(@ToTheWeek AS nvarchar(2))
    ELSE
    CAST(@ToTheYear AS nvarchar(4)) + CAST(@ToTheWeek AS nvarchar(2))
    END

    FETCH NEXT FROM curQuantityOrdered INTO @FromTheYear, @FromTheWeek, @ToTheYear, @ToTheWeek, @CatalogName, @ItemNumber, @Warehouse, @QuantityOrdered
    END
    CLOSE curQuantityOrdered
    DEALLOCATE curQuantityOrdered
    GO

  9. #9
    Join Date
    Mar 2002
    Posts
    192
    It Looks like alot but is really 2 steps. The first BLOB of TSQL creates the cursor. The second BLOB of TSQL updates the destination table.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WOW! Dude....how long does it take to run?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Mar 2002
    Posts
    192
    This particular sp executes in a fraction of a second. However it is run multiple times and depending on the volume of data required for processing can add up to hours (inconjunction with the other sp's I have running).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •