Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Atzenbrugg, Austria
    Posts
    4

    Question Unanswered: use variable select when declaring cursor

    Hello,

    I have a problem with declaring a cursor in a stored procedure. This is what I've already written:

    Code:
    declare @select varchar(2000)
    declare @searchfor varchar(200)
    declare @group1_search varchar(30)
    
    set @searchfor = '%sql%'
    set @group1_search = '0,1,2,3,4,5,6,7,8'
    
    DECLARE @TEMP_TABLE table (BegriffID smallint, Comment varchar(500), Group1_ID smallint)
    
    set @select = 'SELECT DISTINCT BEGRIFF.[ID], BEGRIFF.Comment, BEGRIFF.Group1_ID 
       FROM BEGRIFF LEFT OUTER JOIN BEGRIFFDETAIL ON BEGRIFF.[ID] = BEGRIFFDETAIL.Begriff_ID 
       WHERE BEGRIFFDETAIL.[Name] LIKE ' + char(39) + @searchfor + char(39) + 
       ' AND BEGRIFF.Group1_ID IN (' + @group1_search + ') '
    
    DECLARE SEARCH_Cursor CURSOR FOR @select
    I want to insert the result of the cursor into the table variable, but SQL server says: "Incorrect syntax near '@select'."

  2. #2
    Join Date
    Sep 2003
    Posts
    212

    Re: use variable select when declaring cursor

    Originally posted by Illessa
    Hello,

    I have a problem with declaring a cursor in a stored procedure. This is what I've already written:

    Code:
    declare @select varchar(2000)
    declare @searchfor varchar(200)
    declare @group1_search varchar(30)
    
    set @searchfor = '%sql%'
    set @group1_search = '0,1,2,3,4,5,6,7,8'
    
    DECLARE @TEMP_TABLE table (BegriffID smallint, Comment varchar(500), Group1_ID smallint)
    
    set @select = 'SELECT DISTINCT BEGRIFF.[ID], BEGRIFF.Comment, BEGRIFF.Group1_ID 
       FROM BEGRIFF LEFT OUTER JOIN BEGRIFFDETAIL ON BEGRIFF.[ID] = BEGRIFFDETAIL.Begriff_ID 
       WHERE BEGRIFFDETAIL.[Name] LIKE ' + char(39) + @searchfor + char(39) + 
       ' AND BEGRIFF.Group1_ID IN (' + @group1_search + ') '
    
    DECLARE SEARCH_Cursor CURSOR FOR @select
    I want to insert the result of the cursor into the table variable, but SQL server says: "Incorrect syntax near '@select'."
    I tired it with the following code and it does not complain. Dont ask me y.. cuz it's retarded... i dunno. But this is what i did

    declare @select varchar(2000)
    declare @searchfor varchar(200)
    declare @group1_search varchar(30)

    set @searchfor = '%sql%'
    set @group1_search = '0,1,2,3,4,5,6,7,8'

    DECLARE @TEMP_TABLE table (BegriffID smallint, Comment varchar(500), Group1_ID smallint)
    DECLARE SEARCH_Cursor CURSOR
    FOR
    SELECT DISTINCT BEGRIFF.[ID], BEGRIFF.Comment, BEGRIFF.Group1_ID
    FROM BEGRIFF LEFT OUTER JOIN BEGRIFFDETAIL ON BEGRIFF.[ID] = BEGRIFFDETAIL.Begriff_ID
    WHERE BEGRIFFDETAIL.[Name] LIKE ' + char(39) + @searchfor + char(39) +
    ' AND BEGRIFF.Group1_ID IN (' + @group1_search + ')

  3. #3
    Join Date
    Oct 2003
    Location
    Atzenbrugg, Austria
    Posts
    4
    Your way would work. Just one problem: I can't write the select statement directly to the declare cursor because it is variable. Depending on the parameters I get when calling the stored procedure the select statement varies...

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    Originally posted by Illessa
    Your way would work. Just one problem: I can't write the select statement directly to the declare cursor because it is variable. Depending on the parameters I get when calling the stored procedure the select statement varies...
    Try this:

    exec ('DECLARE SEARCH_Cursor CURSOR FOR '+ @select)

  5. #5
    Join Date
    Oct 2003
    Location
    Atzenbrugg, Austria
    Posts
    4

    Talking

    Great, it works!!!! Thank you! :-)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: use variable select when declaring cursor

    Originally posted by Illessa
    Hello,

    I have a problem with declaring a cursor in a stored procedure. This is what I've already written:

    Code:
    declare @select varchar(2000)
    declare @searchfor varchar(200)
    declare @group1_search varchar(30)
    
    set @searchfor = '%sql%'
    set @group1_search = '0,1,2,3,4,5,6,7,8'
    
    DECLARE @TEMP_TABLE table (BegriffID smallint, Comment varchar(500), Group1_ID smallint)
    
    set @select = 'SELECT DISTINCT BEGRIFF.[ID], BEGRIFF.Comment, BEGRIFF.Group1_ID 
       FROM BEGRIFF LEFT OUTER JOIN BEGRIFFDETAIL ON BEGRIFF.[ID] = BEGRIFFDETAIL.Begriff_ID 
       WHERE BEGRIFFDETAIL.[Name] LIKE ' + char(39) + @searchfor + char(39) + 
       ' AND BEGRIFF.Group1_ID IN (' + @group1_search + ') '
    
    DECLARE SEARCH_Cursor CURSOR FOR @select
    I want to insert the result of the cursor into the table variable, but SQL server says: "Incorrect syntax near '@select'."
    Why are you using a cursor at all?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Atzenbrugg, Austria
    Posts
    4
    I have to work on the returned data of the cursor. Within the
    WHILE @@FETCH_STATUS = 0
    BEGIN

    END
    some selects are carried out. Depending on the result of them a table variable will be filled.

Posting Permissions

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