Declare dynamic Cursor from String

Hi,
is it possible to create a cursor from a dynamic string?
Like:

DECLARE @.cursor nvarchar(1000)
SET @.cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @.cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
PeppiNot within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoropen" and a few others with "sp_cursor*" which might
be abloe to do the job for you.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<peppi911@.hotmail.com> wrote in message
news:1146129244.595060.254470@.v46g2000cwv.googlegr oups.com...
> Hi,
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
> = 'aktiv'
> Havn't found anything in the net...
> Thanks,
> Peppi|||(peppi911@.hotmail.com) writes:
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
>= 'aktiv'

Yes, this is possible, but the question remains: why?

See here for details: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.

Thanks,
mike|||peppi911@.hotmail.com wrote:
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That doesn't explain why you are using a cursor. It also doesn't
explain the need for dynamic SQL. Both are things you should avoid when
you can, I think that was what Erland was trying to get at.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(peppi911@.hotmail.com) writes:
> Thanks for your answers.
> I'll have a look at the link.
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That does not explain the cursor - but could be that there is some
calculations are too complex to be carried out set-based. But there is
all reason to avoid the iteration if possible and handle all rows at
once. If there are many products this could mean serious reduction in
execution time.

On the other hand, there is enough information for me to tell that you
don't need any dynamic SQL. There are two possible solutions:

DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND (@.runforall = 1 OR fund_amt > 0)

If there is an index on the selection column for active products, it's
better to do:

IF @.runforall = 1
BEGIN
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
END
ELSE
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND fund_amt > 0
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com

Debugging SQL statement

I am working in SQL Server Reporting Services, and I have an error using a dynamic parameter. The error surfaces during runtime. I believe I could fix the parameter if I knew exactly what the resulting SQL statement looks like. So the question is: how do I view the actual SQL statement that is being executed in a report? I tried SQL Profiler, which shows how my parameter drop-down lists are being populated, but it does not show the main dataset SQL statement.

Alternatively, are there any examples of using a dynamic parameter that is a "datetime" data type?

Any help would be appreciated. Thanks.

Hello,

When are you getting the error, after setting your parameters when you click "View Report"?

You should be capturing the 'Exception' and 'SQL:BatchStarting' events in your trace. This will first whenever the report is started (regardless of error) and any error from the SQL statement.

Of course, if you have the RDL file, you can always look in there for the SQL command under the <CommandText> tag.

Hope this helps.

Jarret

|||

Thanks for responding.

Yes, I am getting the error after I click "View Report". I have the RDL file, but next to the <CommandText> tag, I see the same statement construct that I used in the Dataset (i.e. I see the IIf(parameter!... structure instead of an actual SQL statement) . I'll check out the trace again, but I'm certain that the only SQL statements that were in there pertain to setting up the parameter lists (e.g. "select salesrep from sourcetable").

Thanks again.

|||

Were you able to set up the trace and see the exception and the main dataset's SQL statement? Do you get the error when you're in BIDS using the preview tab? If so, you should be able to see the error (it may be in the output window, Ctrl+Alt+O).

If you have multiple dataset's there should be a <CommandText> tag for each one in your RDL file. Inside of each of these should be the SQL query. Are you saying you have references to Parameters!... in your dataset? Your dataset command should only contain a valid SQL statement, as this is what is sent to the DB server to be executed.

Go to the data tab in BIDS and select your main dataset from the 'Dataset:' dropdown. It should load that command (from the RDL file), then hit the red Exclamation button to run the query. Do you get an error?

Jarret