how to use cursors with strings

[srinivas_mca2k]

hai,
I stored some sql statement in a query and trying to execute it under cursor. But it is giving some error.

declare @str as nvarchar(1000)
set @str = N'select [web-co-num],[inst-date] from [co-scheme]'

declare bill cursor for
exec sp_executesql @str

deallocate bill

I am trying in normal execute also.

declare @str as nvarchar(1000)
set @str = N'select [web-co-num],[inst-date] from [co-scheme]'

declare bill cursor for
exec (@str)

deallocate bill

In both cases i have failed. this is a simple example query. But i want a cursor in my sp, which the string variable contains big dynamic query.

So. please help me to solve this issue.

[qball]

In both cases i have failed. this is a simple example query. But i want a cursor in my sp, which the string variable contains big dynamic query.

do you want a:

'simple example query'
OR a:
cursor, (result set)
???

what is your problem?
can you execute SQL manually? if that fails, DB will tell why.
if can execute, try programatically, if fails, determine EXACTLY what SQL stmt the program is trying to execute!

sql="SELECT * FROM user_tbl;";
print sql;
execute sql;

when/if "sql" fails, execute manually, DB will tell why...

[srinivas_mca2k]

hi,
thanks for ur reply.
My actual problem is as follows:

declare @str as varchar(500)
set @str = 'select * from authors'

exec (@str) -------(1)

declare bill cursor for
exec (@str) -----(2)


-----------------------------------------------------------------------------

if we execute the above first three statements (at st(1)), its giving the records in the authors table.

if i execute the whole statements execpt (1), i.e. trying to execute the sql statement under cursor, its giving error. The error description is as follows:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'exec'.

I want to execute a query string, under a cursor. so that i can process on record by record.

[Creosote]

Do the whole thing dynamically:

DECLARE @sql NVARCHAR(1024)
SET @sql = 

'DECLARE @SpeakerID INT
DECLARE @SpeakerDesc NVARCHAR(50)

DECLARE cur1 CURSOR LOCAL FOR SELECT * FROM Speakers
OPEN cur1
FETCH NEXT FROM cur1 INTO @SpeakerID, @SpeakerDesc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT CONVERT(NVARCHAR(5),@SpeakerID) + '', '' + @SpeakerDesc
	FETCH NEXT FROM cur1 INTO @SpeakerID, @SpeakerDesc
END
CLOSE cur1
DEALLOCATE cur1'

EXEC(@sql)

[qball]

a 'cursor' is DB speak for resultset.
sp is stored proc, is DB speak for stored procedure/logic/program.
usually works, if you can define how.

Do the whole thing dynamically:

that may work.

can you do it 'dynamically' if can't do statically?

[Creosote]

Judginig by his syntax, it appears he is using sql server. By saying doing it dynamically, I meant using dynamic sql : using transact-sql itself to feed a statement to execute.

Obviously, he wants a "cursor" to be able to loop through a result set from within transact-sql.

[qball]

Judginig by his syntax, it appears he is using sql server.

'he' has the problem, let em describe further, including DB and client info...if one describes problem, maybe understand better?

Obviously, he wants a "cursor" to be able to loop through a result set from within transact-sql.

obviously you "assume".

a 'cursor' is DB speak for resultset.

If you can't execute a static SQL query, I've NEVER seen an example of doing it dynamically solving problem, obviously.

[Creosote]

Then obviously you don't have experience with every DBMS.

He is using sp_executesql. I don't know of any other DBMS that uses that exact same stored procedure name.

He IS trying to get a cursor off of a resultset based on tell that sp_executesql procedure to execute a variable string. That part is easy to see. It however does not work.

My suggestion is to put the statement telling the DBMS to execute a variable string outside of the cursor declaration -- which does work.

If you can't execute a static SQL query, I've NEVER seen an example of doing it dynamically solving problem, obviously.

Then read

[vass0922]

I had to write a dynamic SP once.. all the table names were variables.. can't do that in SQL (at least not in sql 7) so it made it a bit difficult

Worked fine beyond that though

[qball]

If you can't execute a static SQL query, I've NEVER seen an example of doing it dynamically solving problem, obviously.

Then obviously you don't have experience with every DBMS.

CORRECT!

BUT all DBMS, me havum experienced use SQL!

before SQL, DOS/lotus123/dbase1-4?

anyone that knows SQL, knows:
Show me the SQL DBMS executing, OR NOT?

problem solved, period