Cursors and dynamic SQL statements


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Cursors and dynamic SQL statements

  1. #1
    Keoki Guest

    Cursors and dynamic SQL statements


    I don't even know if this is possible, but I sure hope it is. Please be patient.
    I am fairly new to SQL Server.

    I build an SQL statement depending on responses from the user and then use
    the EXECUTE command to run it. I want to pass the results of that EXECUTE
    command to a cursor, but I am definitely doing something wrong, because my
    cursor remains empty.

    I have used Cursors before with a static select statement, but I can't figure
    out how to do it with a dynamic statement. For example, my EXECUTE command
    would be:

    EXECUTE(@Select_String + @Where_String).

    If you can help, I will be eternally grateful. This is my first time in
    this forum and I don't know if it will let me know of any responses, so please
    feel free to e-mail me at gb@cardinalhill.org

    Thank you,

    Keoki

  2. #2
    Sean Othule Guest

    Re: Cursors and dynamic SQL statements


    try this below - you need to take the two params
    and create one



    select @total_string = @select string + @where_string

    EXECUTE(@total_string)


    or here's an example of the full thing--------------


    Declare xxx_cursor CURSOR
    FOR
    select name from sysobjects where type = "U"
    GO
    SET NOCOUNT ON
    DECLARE @sproc varchar(8)
    DECLARE @table varchar(50)
    DECLARE @stat varchar(100)
    DECLARE @counter int

    SELECT @counter = 0
    SELECT @sproc = 'sp_help '

    OPEN xxx_cursor
    FETCH xxx_cursor INTO @table
    WHILE @@FETCH_STATUS = 0
    begin

    SELECT @stat = @sproc + @table

    exec (@stat )

    SELECT @counter = @counter + 1
    FETCH xxx_cursor INTO @table

    END
    CLOSE xxx_cursor
    DEALLOCATE xxx_cursor
    GO









    "Keoki" <gb@cardinalhill.org> wrote:
    >
    >I don't even know if this is possible, but I sure hope it is. Please be

    patient.
    > I am fairly new to SQL Server.
    >
    >I build an SQL statement depending on responses from the user and then use
    >the EXECUTE command to run it. I want to pass the results of that EXECUTE
    >command to a cursor, but I am definitely doing something wrong, because

    my
    >cursor remains empty.
    >
    >I have used Cursors before with a static select statement, but I can't figure
    >out how to do it with a dynamic statement. For example, my EXECUTE command
    >would be:
    >
    >EXECUTE(@Select_String + @Where_String).
    >
    >If you can help, I will be eternally grateful. This is my first time in
    >this forum and I don't know if it will let me know of any responses, so

    please
    >feel free to e-mail me at gb@cardinalhill.org
    >
    >Thank you,
    >
    >Keoki



  3. #3
    Keoki Guest

    Re: Cursors and dynamic SQL statements


    Hi again,

    I am still doing something wrong. I created a variable to hold the two params
    and then used the variable in the execute statement for the cursor.

    Below is part of the code:

    SELECT @SQLStringBKP = ('SET Quoted_Identifier OFF ' + Select_StringBKP
    + @Where_StringBKP + @Order_StringBKP)


    DECLARE @tempCounter INT, @tempEmpKey INT, @EmpKey INT, @EmpID VarChar(20),
    @LName VarChar(50), @FName VarChar(50), @BodyCode VarChar(20), @BCType VarChar(20),
    @BCSubType VarChar(20), @ChgMstKey INT, @ChgMstCode VarChar(20), @CMDType
    VarChar(10), @CMDSubType VarChar(10), @CMDDesc VarChar(30)

    DECLARE @Testing_RS CURSOR
    FOR
    EXECUTE(@SQLStringBKP)

    OPEN @Testing_RS

    FETCH NEXT FROM @Testing_RS INTO @EmpKey, @EmpID, @LName, @FName, @BodyCode,
    @BCType,@BCSubType, @ChgMstKey, @ChgMstCode, @CMDType, @CMDSubType, @CMDDesc



    But when I try to save it I get a message that there is incorrect syntax
    near the keyword FOR. If I delete FOR, then I am able to save, but of course,
    when it runs the results from the execute statement are not put into the
    cursor. When I step through the code via Visual Basic and the T-SQL Debugger,
    the variables that I have fetched into remain empty.

    I don't see anything different between my code and your example or the examples
    in the books, but I must definitely be doing something wrong. Any ideas?

    Thanks,

    Keoki


    "Sean Othule" <othule@hotmail.com> wrote:
    >
    >try this below - you need to take the two params
    >and create one
    >
    >
    >
    >select @total_string = @select string + @where_string
    >
    > EXECUTE(@total_string)
    >
    >
    >or here's an example of the full thing--------------
    >
    >
    >Declare xxx_cursor CURSOR
    >FOR
    > select name from sysobjects where type = "U"
    >GO
    >SET NOCOUNT ON
    >DECLARE @sproc varchar(8)
    >DECLARE @table varchar(50)
    >DECLARE @stat varchar(100)
    >DECLARE @counter int
    >
    >SELECT @counter = 0
    >SELECT @sproc = 'sp_help '
    >
    >OPEN xxx_cursor
    >FETCH xxx_cursor INTO @table
    > WHILE @@FETCH_STATUS = 0
    > begin
    >
    > SELECT @stat = @sproc + @table
    >
    > exec (@stat )
    >
    >SELECT @counter = @counter + 1
    > FETCH xxx_cursor INTO @table
    >
    > END
    >CLOSE xxx_cursor
    >DEALLOCATE xxx_cursor
    >GO
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >"Keoki" <gb@cardinalhill.org> wrote:
    >>
    >>I don't even know if this is possible, but I sure hope it is. Please be

    >patient.
    >> I am fairly new to SQL Server.
    >>
    >>I build an SQL statement depending on responses from the user and then

    use
    >>the EXECUTE command to run it. I want to pass the results of that EXECUTE
    >>command to a cursor, but I am definitely doing something wrong, because

    >my
    >>cursor remains empty.
    >>
    >>I have used Cursors before with a static select statement, but I can't

    figure
    >>out how to do it with a dynamic statement. For example, my EXECUTE command
    >>would be:
    >>
    >>EXECUTE(@Select_String + @Where_String).
    >>
    >>If you can help, I will be eternally grateful. This is my first time in
    >>this forum and I don't know if it will let me know of any responses, so

    >please
    >>feel free to e-mail me at gb@cardinalhill.org
    >>
    >>Thank you,
    >>
    >>Keoki

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center