CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Radislav
Radislav

Reputation: 2983

How to change cursor's select statement by some condition?

Does any body know how to set different select statement for the same cursor? I need somethink like this.

DECLARE Temp_Cursor CURSOR FOR
IF(@TempVar = 1)
BEGIN
    SELECT CustomerId FROM Customers
END
ELSE IF(@TempVar = 2)
BEGIN
    SELECT OrderId FROM Orders
END
OPEN Temp_Cursor;
FETCH NEXT FROM TempCursor INTO @TempObjectId
WHILE @@FETCH_STATUS = 0
BEGIN

.... etc

I have found solution: DECLARE @Temp_Cursor CURSOR IF(@TempVar = 1) BEGIN SET @Temp_Cursor = CURSOR FOR SELECT CustomerId FROM Customers END ELSE IF(@TempVar = 2) BEGIN SET @Temp_Cursor = CURSOR FOR SELECT OrderId FROM Orders END OPEN @Temp_Cursor; FETCH NEXT FROM @TempCursor INTO @TempObjectId WHILE @@FETCH_STATUS = 0 BEGIN

Upvotes: 2

Views: 7341

Answers (2)

aF.
aF.

Reputation: 66697

Put the IF outside and do two different cursors, one for each situation.

Like this:

IF(@TempVar = 1)
BEGIN

    DECLARE Temp_Cursor CURSOR FOR
    SELECT CustomerId FROM Customers


    OPEN Temp_Cursor;
    FETCH NEXT FROM TempCursor INTO @TempObjectId
    WHILE @@FETCH_STATUS = 0
    BEGIN 
    ....
END
ELSE IF(@TempVar = 2)
BEGIN

    DECLARE Temp_Cursor CURSOR FOR
    SELECT OrderId FROM Orders


    OPEN Temp_Cursor;
    FETCH NEXT FROM TempCursor INTO @TempObjectId
    WHILE @@FETCH_STATUS = 0
    BEGIN 
    ....
END

Another possibility is to use Dynamic SQL.

Upvotes: 1

Pankaj
Pankaj

Reputation: 10095

The complete query should be dynamic. That means the execution plan will come out at run time.

Example

Declare @SelectStatement Varchar(50)
Declare @SQL Varchar(50)

IF(@TempVar = 1)
BEGIN
    Set @SelectStatement = SELECT CustomerId FROM Customers
END
ELSE IF(@TempVar = 2)
BEGIN
    Set @SelectStatement = SELECT OrderId FROM Orders
END

Set @SQL = 'DECLARE Temp_Cursor CURSOR FOR
OPEN Temp_Cursor;
FETCH NEXT FROM TempCursor INTO ' + @TempObjectId +
'WHILE @@FETCH_STATUS = 0
BEGIN '

Upvotes: 1

Related Questions