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 SP3the 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.|||You can't declare with dynamic sql...

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...|||Actually you're both wrong. I figured it out.

Turns out you need to use the EXEC command to execute the string.|||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:

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...|||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.|||Can you post the sproc?

I'd like to see if there's a non cursor way...|||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|||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.|||WOW! Dude...how long does it take to run?|||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).