Cursor SQL

ana · August 25, 2015, 2:50pm

Hi everyone!
How can i use a cursor in this case?

USE [SBO_SIXLONDON_PRD]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[DifferencesBetweenDatabase]

       @Number INTEGER
AS
BEGIN

       SET NOCOUNT ON;

       -- Para a Base de Dados da Brandão & Soares
	   IF @Number = 1
             BEGIN
			 
			 --OCRG
			 SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_BRD_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))
			--OUGP
			SELECT UgpEntry, UgpCode FROM SBO_SIXLONDON_PRD.DBO.OUGP AS T3
				WHERE (NOT EXISTS (SELECT UgpEntry, UgpCode FROM SBO_BRD_PRD.DBO.OUGP AS T4 WHERE (UgpEntry = T3.UgpEntry) OR (UgpCode = T3.UgpCode)))
			--OUOM
			SELECT UomEntry, UomCode FROM SBO_SIXLONDON_PRD.DBO.OUOM AS T5
				WHERE (NOT EXISTS (SELECT UomEntry, UomCode FROM SBO_BRD_PRD.DBO.OUOM AS T6 WHERE (UomEntry = T5.UomEntry) OR (UomCode = T5.UomCode)))
			--OCYC
			SELECT Code, Name FROM SBO_SIXLONDON_PRD.DBO.OCYC AS T7
				WHERE (NOT EXISTS (SELECT Code, Name FROM SBO_BRD_PRD.DBO.OCYC AS T8 WHERE (Code = T7.Code) OR (Name = T7.Name)))
			--OAGP
			SELECT AgentCode, AgentName FROM SBO_SIXLONDON_PRD.DBO.OAGP AS T9
				WHERE (NOT EXISTS (SELECT AgentCode, AgentName FROM SBO_BRD_PRD.DBO.OAGP AS T10 WHERE (AgentCode = T9.AgentCode) OR (AgentName = T9.AgentName)))
			--ODUT
			SELECT TermCode, TermName FROM SBO_SIXLONDON_PRD.DBO.ODUT AS T11
				WHERE (NOT EXISTS (SELECT TermCode, TermName FROM SBO_BRD_PRD.DBO.ODUT AS T12 WHERE (TermCode = T11.TermCode) OR (TermName = T11.TermName)))
			--OOND
			SELECT INDCODE, INDNAME FROM SBO_SIXLONDON_PRD.DBO.OOND AS T13
				WHERE (NOT EXISTS (SELECT INDCODE, INDNAME FROM SBO_BRD_PRD.DBO.OOND AS T14 WHERE (INDCODE = T13.INDCODE) OR (INDNAME = T13.INDNAME)))
			--OCRN
			SELECT CurrCode, CurrName FROM SBO_SIXLONDON_PRD.DBO.OCRN AS T15
				WHERE (NOT EXISTS (SELECT CurrCode, CurrName FROM SBO_BRD_PRD.DBO.OCRN AS T16 WHERE (CurrCode = T15.CurrCode) OR (CurrName = T15.CurrName)))
			--OSHP
			SELECT TrnspCode, TrnspName FROM SBO_SIXLONDON_PRD.DBO.OSHP AS T17
				WHERE (NOT EXISTS (SELECT TrnspCode, TrnspName FROM SBO_BRD_PRD.DBO.OSHP AS T18 WHERE (TrnspCode = T17.TrnspCode) OR (TrnspName = T17.TrnspName)))
			--OITB
			SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SIXLONDON_PRD.DBO.OITB AS T19
				WHERE (NOT EXISTS (SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_BRD_PRD.DBO.OITB AS T20 WHERE (ItmsGrpCod = T19.ItmsGrpCod) OR (ItmsGrpNam = T19.ItmsGrpNam)))
			--OSLP
			SELECT SlpCode, SlpName FROM SBO_SIXLONDON_PRD.DBO.OSLP AS T21
				WHERE (NOT EXISTS (SELECT SlpCode, SlpName FROM SBO_BRD_PRD.DBO.OSLP AS T22 WHERE (SlpCode = T21.SlpCode) OR (SlpName = T21.SlpName)))
             END

			 -- Para a Base de Dados da Sweet Capsule
			 IF @Number = 2
             BEGIN
			 
			 --OCRG
			 SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_SWT_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))
			--OUGP
			SELECT UgpEntry, UgpCode FROM SBO_SIXLONDON_PRD.DBO.OUGP AS T3
				WHERE (NOT EXISTS (SELECT UgpEntry, UgpCode FROM SBO_SWT_PRD.DBO.OUGP AS T4 WHERE (UgpEntry = T3.UgpEntry) OR (UgpCode = T3.UgpCode)))
			--OUOM
			SELECT UomEntry, UomCode FROM SBO_SIXLONDON_PRD.DBO.OUOM AS T5
				WHERE (NOT EXISTS (SELECT UomEntry, UomCode FROM SBO_SWT_PRD.DBO.OUOM AS T6 WHERE (UomEntry = T5.UomEntry) OR (UomCode = T5.UomCode)))
			--OCYC
			SELECT Code, Name FROM SBO_SIXLONDON_PRD.DBO.OCYC AS T7
				WHERE (NOT EXISTS (SELECT Code, Name FROM SBO_SWT_PRD.DBO.OCYC AS T8 WHERE (Code = T7.Code) OR (Name = T7.Name)))
			--OAGP
			SELECT AgentCode, AgentName FROM SBO_SIXLONDON_PRD.DBO.OAGP AS T9
				WHERE (NOT EXISTS (SELECT AgentCode, AgentName FROM SBO_SWT_PRD.DBO.OAGP AS T10 WHERE (AgentCode = T9.AgentCode) OR (AgentName = T9.AgentName)))
			--ODUT
			SELECT TermCode, TermName FROM SBO_SIXLONDON_PRD.DBO.ODUT AS T11
				WHERE (NOT EXISTS (SELECT TermCode, TermName FROM SBO_SWT_PRD.DBO.ODUT AS T12 WHERE (TermCode = T11.TermCode) OR (TermName = T11.TermName)))
			--OOND
			SELECT INDCODE, INDNAME FROM SBO_SIXLONDON_PRD.DBO.OOND AS T13
				WHERE (NOT EXISTS (SELECT INDCODE, INDNAME FROM SBO_SWT_PRD.DBO.OOND AS T14 WHERE (INDCODE = T13.INDCODE) OR (INDNAME = T13.INDNAME)))
			--OCRN
			SELECT CurrCode, CurrName FROM SBO_SIXLONDON_PRD.DBO.OCRN AS T15
				WHERE (NOT EXISTS (SELECT CurrCode, CurrName FROM SBO_SWT_PRD.DBO.OCRN AS T16 WHERE (CurrCode = T15.CurrCode) OR (CurrName = T15.CurrName)))
			--OSHP
			SELECT TrnspCode, TrnspName FROM SBO_SIXLONDON_PRD.DBO.OSHP AS T17
				WHERE (NOT EXISTS (SELECT TrnspCode, TrnspName FROM SBO_SWT_PRD.DBO.OSHP AS T18 WHERE (TrnspCode = T17.TrnspCode) OR (TrnspName = T17.TrnspName)))
			--OITB
			SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SIXLONDON_PRD.DBO.OITB AS T19
				WHERE (NOT EXISTS (SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SWT_PRD.DBO.OITB AS T20 WHERE (ItmsGrpCod = T19.ItmsGrpCod) OR (ItmsGrpNam = T19.ItmsGrpNam)))
			--OSLP
			SELECT SlpCode, SlpName FROM SBO_SIXLONDON_PRD.DBO.OSLP AS T21
				WHERE (NOT EXISTS (SELECT SlpCode, SlpName FROM SBO_SWT_PRD.DBO.OSLP AS T22 WHERE (SlpCode = T21.SlpCode) OR (SlpName = T21.SlpName)))
             END
            
      -- Para a Base de Dados da Dashing
			 IF @Number = 3
             BEGIN
			 
			 --OCRG
			 SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_DSH_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))
			--OUGP
			SELECT UgpEntry, UgpCode FROM SBO_SIXLONDON_PRD.DBO.OUGP AS T3
				WHERE (NOT EXISTS (SELECT UgpEntry, UgpCode FROM SBO_DSH_PRD.DBO.OUGP AS T4 WHERE (UgpEntry = T3.UgpEntry) OR (UgpCode = T3.UgpCode)))
			--OUOM
			SELECT UomEntry, UomCode FROM SBO_SIXLONDON_PRD.DBO.OUOM AS T5
				WHERE (NOT EXISTS (SELECT UomEntry, UomCode FROM SBO_DSH_PRD.DBO.OUOM AS T6 WHERE (UomEntry = T5.UomEntry) OR (UomCode = T5.UomCode)))
			--OCYC
			SELECT Code, Name FROM SBO_SIXLONDON_PRD.DBO.OCYC AS T7
				WHERE (NOT EXISTS (SELECT Code, Name FROM SBO_DSH_PRD.DBO.OCYC AS T8 WHERE (Code = T7.Code) OR (Name = T7.Name)))
			--OAGP
			SELECT AgentCode, AgentName FROM SBO_SIXLONDON_PRD.DBO.OAGP AS T9
				WHERE (NOT EXISTS (SELECT AgentCode, AgentName FROM SBO_DSH_PRD.DBO.OAGP AS T10 WHERE (AgentCode = T9.AgentCode) OR (AgentName = T9.AgentName)))
			--ODUT
			SELECT TermCode, TermName FROM SBO_SIXLONDON_PRD.DBO.ODUT AS T11
				WHERE (NOT EXISTS (SELECT TermCode, TermName FROM SBO_DSH_PRD.DBO.ODUT AS T12 WHERE (TermCode = T11.TermCode) OR (TermName = T11.TermName)))
			--OOND
			SELECT INDCODE, INDNAME FROM SBO_SIXLONDON_PRD.DBO.OOND AS T13
				WHERE (NOT EXISTS (SELECT INDCODE, INDNAME FROM SBO_DSH_PRD.DBO.OOND AS T14 WHERE (INDCODE = T13.INDCODE) OR (INDNAME = T13.INDNAME)))
			--OCRN
			SELECT CurrCode, CurrName FROM SBO_SIXLONDON_PRD.DBO.OCRN AS T15
				WHERE (NOT EXISTS (SELECT CurrCode, CurrName FROM SBO_DSH_PRD.DBO.OCRN AS T16 WHERE (CurrCode = T15.CurrCode) OR (CurrName = T15.CurrName)))
			--OSHP
			SELECT TrnspCode, TrnspName FROM SBO_SIXLONDON_PRD.DBO.OSHP AS T17
				WHERE (NOT EXISTS (SELECT TrnspCode, TrnspName FROM SBO_DSH_PRD.DBO.OSHP AS T18 WHERE (TrnspCode = T17.TrnspCode) OR (TrnspName = T17.TrnspName)))
			--OITB
			SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SIXLONDON_PRD.DBO.OITB AS T19
				WHERE (NOT EXISTS (SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_DSH_PRD.DBO.OITB AS T20 WHERE (ItmsGrpCod = T19.ItmsGrpCod) OR (ItmsGrpNam = T19.ItmsGrpNam)))
			--OSLP
			SELECT SlpCode, SlpName FROM SBO_SIXLONDON_PRD.DBO.OSLP AS T21
				WHERE (NOT EXISTS (SELECT SlpCode, SlpName FROM SBO_DSH_PRD.DBO.OSLP AS T22 WHERE (SlpCode = T21.SlpCode) OR (SlpName = T21.SlpName)))
			END

			-- Para a Base de Dados da Ripe PT
			 IF @Number = 4
             BEGIN
			 
			 --OCRG
			 SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_PT_RIPE_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))
			--OUGP
			SELECT UgpEntry, UgpCode FROM SBO_SIXLONDON_PRD.DBO.OUGP AS T3
				WHERE (NOT EXISTS (SELECT UgpEntry, UgpCode FROM SBO_PT_RIPE_PRD.DBO.OUGP AS T4 WHERE (UgpEntry = T3.UgpEntry) OR (UgpCode = T3.UgpCode)))
			--OUOM
			SELECT UomEntry, UomCode FROM SBO_SIXLONDON_PRD.DBO.OUOM AS T5
				WHERE (NOT EXISTS (SELECT UomEntry, UomCode FROM SBO_PT_RIPE_PRD.DBO.OUOM AS T6 WHERE (UomEntry = T5.UomEntry) OR (UomCode = T5.UomCode)))
			--OCYC
			SELECT Code, Name FROM SBO_SIXLONDON_PRD.DBO.OCYC AS T7
				WHERE (NOT EXISTS (SELECT Code, Name FROM SBO_PT_RIPE_PRD.DBO.OCYC AS T8 WHERE (Code = T7.Code) OR (Name = T7.Name)))
			--OAGP
			SELECT AgentCode, AgentName FROM SBO_SIXLONDON_PRD.DBO.OAGP AS T9
				WHERE (NOT EXISTS (SELECT AgentCode, AgentName FROM SBO_PT_RIPE_PRD.DBO.OAGP AS T10 WHERE (AgentCode = T9.AgentCode) OR (AgentName = T9.AgentName)))
			--ODUT
			SELECT TermCode, TermName FROM SBO_SIXLONDON_PRD.DBO.ODUT AS T11
				WHERE (NOT EXISTS (SELECT TermCode, TermName FROM SBO_PT_RIPE_PRD.DBO.ODUT AS T12 WHERE (TermCode = T11.TermCode) OR (TermName = T11.TermName)))
			--OOND
			SELECT INDCODE, INDNAME FROM SBO_SIXLONDON_PRD.DBO.OOND AS T13
				WHERE (NOT EXISTS (SELECT INDCODE, INDNAME FROM SBO_PT_RIPE_PRD.DBO.OOND AS T14 WHERE (INDCODE = T13.INDCODE) OR (INDNAME = T13.INDNAME)))
			--OCRN
			SELECT CurrCode, CurrName FROM SBO_SIXLONDON_PRD.DBO.OCRN AS T15
				WHERE (NOT EXISTS (SELECT CurrCode, CurrName FROM SBO_PT_RIPE_PRD.DBO.OCRN AS T16 WHERE (CurrCode = T15.CurrCode) OR (CurrName = T15.CurrName)))
			--OSHP
			SELECT TrnspCode, TrnspName FROM SBO_SIXLONDON_PRD.DBO.OSHP AS T17
				WHERE (NOT EXISTS (SELECT TrnspCode, TrnspName FROM SBO_PT_RIPE_PRD.DBO.OSHP AS T18 WHERE (TrnspCode = T17.TrnspCode) OR (TrnspName = T17.TrnspName)))
			--OITB
			SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SIXLONDON_PRD.DBO.OITB AS T19
				WHERE (NOT EXISTS (SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_PT_RIPE_PRD.DBO.OITB AS T20 WHERE (ItmsGrpCod = T19.ItmsGrpCod) OR (ItmsGrpNam = T19.ItmsGrpNam)))
			--OSLP
			SELECT SlpCode, SlpName FROM SBO_SIXLONDON_PRD.DBO.OSLP AS T21
				WHERE (NOT EXISTS (SELECT SlpCode, SlpName FROM SBO_PT_RIPE_PRD.DBO.OSLP AS T22 WHERE (SlpCode = T21.SlpCode) OR (SlpName = T21.SlpName)))
			END

			
			
			-- Para a Base de Dados da Ripe UK
			 IF @Number = 5
             BEGIN
			 
			 --OCRG
			 SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_UK_RIPE_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))
			--OUGP
			SELECT UgpEntry, UgpCode FROM SBO_SIXLONDON_PRD.DBO.OUGP AS T3
				WHERE (NOT EXISTS (SELECT UgpEntry, UgpCode FROM SBO_UK_RIPE_PRD.DBO.OUGP AS T4 WHERE (UgpEntry = T3.UgpEntry) OR (UgpCode = T3.UgpCode)))
			--OUOM
			SELECT UomEntry, UomCode FROM SBO_SIXLONDON_PRD.DBO.OUOM AS T5
				WHERE (NOT EXISTS (SELECT UomEntry, UomCode FROM SBO_UK_RIPE_PRD.DBO.OUOM AS T6 WHERE (UomEntry = T5.UomEntry) OR (UomCode = T5.UomCode)))
			--OCYC
			SELECT Code, Name FROM SBO_SIXLONDON_PRD.DBO.OCYC AS T7
				WHERE (NOT EXISTS (SELECT Code, Name FROM SBO_UK_RIPE_PRD.DBO.OCYC AS T8 WHERE (Code = T7.Code) OR (Name = T7.Name)))
			--OAGP
			SELECT AgentCode, AgentName FROM SBO_SIXLONDON_PRD.DBO.OAGP AS T9
				WHERE (NOT EXISTS (SELECT AgentCode, AgentName FROM SBO_UK_RIPE_PRD.DBO.OAGP AS T10 WHERE (AgentCode = T9.AgentCode) OR (AgentName = T9.AgentName)))
			--ODUT
			SELECT TermCode, TermName FROM SBO_SIXLONDON_PRD.DBO.ODUT AS T11
				WHERE (NOT EXISTS (SELECT TermCode, TermName FROM SBO_UK_RIPE_PRD.DBO.ODUT AS T12 WHERE (TermCode = T11.TermCode) OR (TermName = T11.TermName)))
			--OOND
			SELECT INDCODE, INDNAME FROM SBO_SIXLONDON_PRD.DBO.OOND AS T13
				WHERE (NOT EXISTS (SELECT INDCODE, INDNAME FROM SBO_UK_RIPE_PRD.DBO.OOND AS T14 WHERE (INDCODE = T13.INDCODE) OR (INDNAME = T13.INDNAME)))
			--OCRN
			SELECT CurrCode, CurrName FROM SBO_SIXLONDON_PRD.DBO.OCRN AS T15
				WHERE (NOT EXISTS (SELECT CurrCode, CurrName FROM SBO_UK_RIPE_PRD.DBO.OCRN AS T16 WHERE (CurrCode = T15.CurrCode) OR (CurrName = T15.CurrName)))
			--OSHP
			SELECT TrnspCode, TrnspName FROM SBO_SIXLONDON_PRD.DBO.OSHP AS T17
				WHERE (NOT EXISTS (SELECT TrnspCode, TrnspName FROM SBO_UK_RIPE_PRD.DBO.OSHP AS T18 WHERE (TrnspCode = T17.TrnspCode) OR (TrnspName = T17.TrnspName)))
			--OITB
			SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_SIXLONDON_PRD.DBO.OITB AS T19
				WHERE (NOT EXISTS (SELECT ItmsGrpCod, ItmsGrpNam FROM SBO_UK_RIPE_PRD.DBO.OITB AS T20 WHERE (ItmsGrpCod = T19.ItmsGrpCod) OR (ItmsGrpNam = T19.ItmsGrpNam)))
			--OSLP
			SELECT SlpCode, SlpName FROM SBO_SIXLONDON_PRD.DBO.OSLP AS T21
				WHERE (NOT EXISTS (SELECT SlpCode, SlpName FROM SBO_UK_RIPE_PRD.DBO.OSLP AS T22 WHERE (SlpCode = T21.SlpCode) OR (SlpName = T21.SlpName)))
			END
END

Thanks.
Anita

gbritton · August 25, 2015, 3:39pm

Why? If you have a solution that doesn't use a cursor, stick with that

ana · August 25, 2015, 4:57pm

But that's not the solution that its suppose to be used...

gbritton · August 25, 2015, 4:59pm

According to whom? Don't you want the best solution?

ana · August 26, 2015, 5:25pm

but i wanted to know how to use a cursor in this case

gbritton · August 26, 2015, 5:31pm

aha, well your queries do not naturally lend themselves to a cursor-based approach, but lets take one:

Original

SELECT GROUPCODE, GROUPNAME FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
				WHERE (NOT EXISTS (SELECT GROUPCODE, GROUPNAME FROM SBO_BRD_PRD.DBO.OCRG AS T2 WHERE (GROUPCODE = T1.GROUPCODE) OR (GROUPNAME = T1.GROUPNAME)))

Cursor-based:

DECLARE cur CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT GROUPCODE
      ,GROUPNAME
FROM SBO_SIXLONDON_PRD.DBO.OCRG AS T1
WHERE (
            NOT EXISTS (
                  SELECT GROUPCODE
                        ,GROUPNAME
                  FROM SBO_BRD_PRD.DBO.OCRG AS T2
                  WHERE (GROUPCODE = T1.GROUPCODE)
                        OR (GROUPNAME = T1.GROUPNAME)
                  )
            )

OPEN cur;

DECLARE @GroupCode INT
      ,@GroupName VARCHAR(50)

FETCH NEXT
FROM cur
INTO @GroupCode
      ,@GroupName

WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @GroupCode AS GroupCode
            ,@GroupName AS GroupName

      FETCH NEXT
      FROM cur
      INTO @GroupCode
            ,@GroupName
END

CLOSE cur

DEALLOCATE cur

ana · August 27, 2015, 10:39am

Thank you. I can't see the result yet, i may be doing something wrong. I have to manage that as a TSQL right? Put this code in my inicial code, changing the selection?

Big thank you!

gbritton · August 27, 2015, 1:00pm

In SSMS, if you hilite the query in the DECLARE CURSOR statement and hit f5, do you see results? If not, then its the query that has a problem.

ana · September 1, 2015, 9:44am

Hi. I can't use a parameters to the comparasion table? Like @Database, so i can analyse more than one database in the same query. I mean, like in the example above - that's very primitive. Sorry, i'm new at this:

DECLARE cur CURSOR FAST_FORWARD READ_ONLY
DECLARE @DATABASE VARCHAR (50)

@DATABASE = 'SBO_BRD_PRD.DBO'

FOR
SELECT INDCODE
,INDNAME, 'OOND'

FROM SBO_SIXLONDON_PRD.DBO.OOND AS T1
WHERE (
NOT EXISTS (
SELECT INDCODE
,INDNAME, 'OOND'
FROM @DATABASE.OOND AS T2
WHERE (INDCODE = T1.INDCODE)
OR (INDNAME = T1.INDNAME)
)
)
OPEN cur;

DECLARE @INDCODE INT
,@INDNAME VARCHAR(50), @TABLENAME VARCHAR (10)
FETCH NEXT
FROM cur
INTO @INDCODE
,@INDNAME, @TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @INDCODE AS INDCODE
,@INDNAME AS INDNAME
,@TABLENAME AS TABLENAME

  FETCH NEXT
  FROM cur
  INTO @INDCODE
        ,@INDNAME, @TABLENAME

END

CLOSE cur

DEALLOCATE cur

Thanks,
Anita

Kristen · September 1, 2015, 12:38pm

No, 'fraid not ...

You would have to use Dynamic SQL - that is to say construct a string variable containing the whole SQL, combining your @Database variable with the rest of the SQL, and then execute that.

If you need to use a variable for Database name, Table name, Column name etc. then you have to use Dynamic SQL.

If you want to COMPARE the data IN a Column to a Variable that's fine using a regular SELECT statement ...

Dynamic SQL is more of a pain to debug, because you can't easily see what is going on. It will be a lot more difficult if you need to use a Cursor (in general terms I suggest you try to avoid a cursor, and loops, SQL operates much more effectively if you can operate on "Sets" of data)

If you have to go down the Dynamic SQL route my suggestion would be:

Create the @MySQL string of SQL (i.e. concatenating your SQL statement with your @Database etc. variable)
Use PRINT or SELECT to output the generated SQL
Try executing that SQL yourself, manually

You can do:

BEGIN TRANSACTION
... your test SQL Cut & Pasted here ...
ROLLBACK

so that there are no side effects (obvious, or "hidden"!!)

Adjust the test SQL until it is right, and then put those changes back into the original code that generated the SQL, then Rinse & Repeat from #2
Once the code is correctly generating valid SQL then change the PRINT / SELECT to

EXEC (@MySQL)

Your cursor appears to be just selecting INDCODE, INDNAME & TABLENAME outputting one row per iteration of the cursor?? It would be much (as in Much MUCH!!) better to SELECT it as a single select for all matching rows in the table rather than RBAR (Row By Agonising Row). The APP can then loop round the multi-row resultset "processing" each row. ("Processing" might just be "displaying")

Then perhaps Loop to select a different Database to query.