Cursors and dynamic SQL statements

[Keoki]

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

[Sean Othule]

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

[Keoki]

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
>