Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Location
    Brazil - Rio de Janeiro
    Posts
    7

    Question Unanswered: How to open a cursor using EXEC command?

    Hi anyone,

    I've been working with Oracle since long date ago, but now I need to do a conversion of my (PL/SQL) Procs/Funcs/Trigs into SQLServer and I need to know how can I do the following two e.g. using SQL Server (T-SQL):

    a)
    wSQLRUN := 'SELECT A_ID, A_DISC, A_DESC, A_VAL FROM ASSIGNMENT WHERE C_ID = 10';

    EXECUTE IMMEDIATE wSQLRUN INTO wQID, wQDISC, wQDESC, wQVAL1;

    b)
    wSQL := 'SELECT * FROM TAB1';

    OPEN CUR_COL FOR wSQL;

    LOOP
    FETCH CUR_COL INTO COL_REC;
    EXIT WHEN CUR_COL%NOTFOUND;
    END LOOP;

    Thanks for any help!

    Regards,
    Bruno.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    a) 'SELECT A_ID, A_DISC, A_DESC, A_VAL FROM ASSIGNMENT WHERE C_ID = 10';

    Answer:
    SELECT @wQID = A_ID, @wQDISC=A_DISC, @wQDESC=A_DESC, @wQVAL1=A_VAL
    FROM ASSIGNMENT
    WHERE C_ID = 10

    You must DECLARE the variables @wQID, @wQDISC, @wQDESC, @wQVAL1. See the online help with SQL - Books Online

    b) Here is a template that comes with SQL Server 2000

    Code:
    -- =============================================
    -- Declare and using a READ_ONLY cursor
    -- =============================================
    DECLARE <cursor_name, sysname, test_cursor> CURSOR
    READ_ONLY
    FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
    
    DECLARE @name varchar(40)
    OPEN <cursor_name, sysname, test_cursor>
    
    FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    --		PRINT 'add user defined code here'
    --		eg.
    		DECLARE @message varchar(100)
    		SELECT @message = 'my name is: ' + @name
    		PRINT @message
    	END
    	FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
    END
    
    CLOSE <cursor_name, sysname, test_cursor>
    DEALLOCATE <cursor_name, sysname, test_cursor>
    GO
    Again see Books online for more help.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •