Select from @DatabaseName

Question

I need a database variable because the tables from which I'm accessing data will be in various databases, such as dBase1, dBase2, etc. I cannot use Select... in a string, because I want to use the Select statement into a cursor, which does not appear to allow the use of strings and the exec command. Here is an example that does not work. Any advice is appreciated...

DECLARE @fromDBase varchar(10), @FundCD char(4), @actID INT, 
    @PortCode char(4), @FundDecm INT, @PlanCd char(6)
set @FundCD  = 'F0XX'
set @actID = 11135408   
set @PortCode = 'P001'
set @FundDecm = 3
set @PlanCd = 'XXX001'
DECLARE csFin cursor STATIC LOCAL for
SELECT PortionCode, CashValue, UnitValue, CostValue, UniqueID
from (SELECT DatabaseName FROM [dBase1].[dbo].mPTLDetail WHERE plancode = @PlanCd)
WHERE AccountID = @actID 
    AND FundCode = @FundCd 
    AND UnitValue != 0
    AND (Cast(Str(UnitValue, 12, @FundDecm) as money) - UnitValue) != 0

score 0 · Answer 1 · answered Jun 04 '11 at 14:36

I think you would need to build a dynamic statement and call exec sp_executesql to execute it. Not sure how this would affect the cursor though, I am not too familiar with them.

DECLARE @fromDBase varchar(10), @FundCD char(4), @actID INT, 
    @PortCode char(4), @FundDecm INT, @PlanCd char(6), 
    @sqlstatement nvarchar(4000)
set @FundCD  = 'F0XX'
set @actID = 11135408   
set @PortCode = 'P001'
set @FundDecm = 3
set @PlanCd = 'XXX001'

SELECT @fromDBase = DatabaseName FROM [dBase1].[dbo].mPTLDetail WHERE plancode = @PlanCd

set @sqlstatement = 'DECLARE csFin cursor STATIC LOCAL for SELECT PortionCode, CashValue,
UnitValue, CostValue, UniqueID from' + @fromDBase + 'WHERE AccountID = ' + @actID + ' AND     
FundCode = ' + @FundCd + ' AND UnitValue != 0 AND (Cast(Str(UnitValue, 12, ' + @FundDecm  
+') as money) - UnitValue) != 0'

exec sp_executesql @sqlstatement

this [question](http://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure) might be of help also — coryT, Jun 04 '11 at 14:38

score 0 · Answer 2 · answered Jun 04 '11 at 16:00

Cursors can assigned dynamically by using a cursor variable together with sp_executesql.

CREATE PROCEDURE GetData
    @FundCD char(4), @actID int, @PortCode char(4), @FundDecm int,
    @PlanCd char(6)
AS

DECLARE @fromDBase varchar(10), @query nvarchar(max), @cursor cursor

SET @fromDBase =
(
    SELECT DatabaseName
    FROM [dBase1].[dbo].mPTLDetail
    WHERE plancode = @PlanCd
)

SET @query = N'
    SET @cursor = CURSOR FOR
        SELECT PortionCode, CashValue, UnitValue, CostValue, UniqueID
        FROM ' + QUOTENAME(@fromDBase) + '.[dbo].[Table1]
        WHERE AccountID = @actID
            AND FundCode = @FundCd
            AND UnitValue != 0
            AND (Cast(Str(UnitValue, 12, @FundDecm) as money) - UnitValue) != 0;

    OPEN @cursor;'

EXEC sp_executesql @query,
    N'@FundCD char(4), @actID int, @FundDecm int, @cursor cursor out',
    @FundCD, @actID, @FundDecm, @cursor out

FETCH NEXT FROM @cursor

Select from @DatabaseName

2 Answers2