CODEWITHSUNDEEP
sql-server-2008t-sql
HKImpact
HKImpact

Reputation: 620

Alternative to Cursor

I am looking for an alternative way to using a cursor for a stored procedure. Instead of selecting each Database (LAL, SINC, SMSS) like I do below, I would like to select a list of strings from a separate table and insert them into the @rates table, joined with there corresponding db/table. Is there a way to do this with a join, or will I need to write this with Dynamic SQL/Cursor?

I'm looking for possible solutions and appreciate all advice. Thanks. 

DECLARE @rates TABLE
(
    DB       CHAR(5),
    FUTASUTA CHAR(3),
    DSCRIPTN CHAR(31),
    FUSUTXRT DECIMAL(18,4)
)

-- LAL Rates
INSERT INTO @rates 
    SELECT 'LAL', FUTASUTA, DSCRIPTN, (CONVERT(DECIMAL(18,4),FUSUTXRT))/10000000 as FUSUTXRT 
    FROM [LAL].[dbo].[UPR40100]

-- SINC Rates
INSERT INTO @rates 
    SELECT 'SINC', FUTASUTA, DSCRIPTN, (CONVERT(DECIMAL(18,4),FUSUTXRT))/10000000 as FUSUTXRT  
    FROM [SINC].[dbo].[UPR40100]

-- SMSS Rates
INSERT INTO @rates 
    SELECT 'SMSS', FUTASUTA, DSCRIPTN, (CONVERT(DECIMAL(18,4),FUSUTXRT))/10000000 as FUSUTXRT  
    FROM [SMSS].[dbo].[UPR40100]

....etc

The table with Distinct databases is a simple table with names/id's

id | Name

1 | LAL

2 | SINC

3 | SMSS

etc...

Upvotes: 0

Views: 65

Answers (1)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Following method will work for you :

DECLARE @TABLE TABLE(DBNAME VARCHAR(50))
DECLARE @DYNAMICQUERY VARCHAR(MAX)

INSERT INTO @TABLE VALUES('LAL')
INSERT INTO @TABLE VALUES('SINC')
INSERT INTO @TABLE VALUES('SMSS')

SET @DYNAMICQUERY = 
(
SELECT 'INSERT INTO @rates SELECT '''+ DBNAME +''', FUTASUTA,DSCRIPTN, (CONVERT(DECIMAL(18,4),FUSUTXRT))/10000000 as FUSUTXRT FROM ['+ DBNAME + '].[dbo].[UPR40100];'
FROM @TABLE
FOR XML PATH('')
)

EXEC(@DYNAMICQUERY)

Upvotes: 2

Related Questions