Default cursor in SQL 2000

Hello,

I am writing an application which I would like to use a server cursor on. I have noticed that when I try to access a table that I have created using a server cursor with my app, I have problems getting the information. It says that the cursor doesn't support bookmarks. But, when I access the Employees table on the Northwind database with the same settings, everything is fine. Is there some sort of option I'm missing with my tables? Some default cursor or something? Is there a way to tell what cursors are defined with the Northwind DB? Any help would be great, thanks!Check if your table has a primary key|||that was it, thank you very much!

Originally posted by kukuk
Check if your table has a primary key|||Would something similar also go for the dynamic cursor? I setup the primary keys which works great for Keyset type. But I get the old 'Dataset does not support bookmarks' when trying a dynamic cursor on the same dataset. I've tried both MSDASQL and SQLOLEDB providers, both gave errors.

Originally posted by Thread77
that was it, thank you very much!sql

declaring cursors

I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

EXECUTE Select + @.LastNameField + ', ' + @.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' + @.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

so you'll can know if your statement is correct.

bye,
Maritzita

Originally posted by plineham
I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3|||To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

Thanks for your help|||Great...cursors are bad enough, noe we have dynamicx sql cursors...

what are you trying to do?

I hope it's an admin function...

(as opposed to an applcation function)

I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

so...

why bother?|||We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me|||You dude..no problem...just trying to help you..

What do you mean by building keys?

And this..

Building the keys within SQL would be even more complicated.

Doesn't make sense to me...

mostly because I don't understand what building keys means...

In any event...I hope it's working for you...

AND it's FRIDAY...yeeeeHaaaaaaa

Now if I can find my lost shaker of salt, I'd be in business..|||It was friday but it is now monday. Aaaaaaa not good at all.

The key I mean is essentially a string containing key components of an address

for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

Thanks for your help anyway|||What are the rules to build a "key" and how do you update it?

Sounds like a job for a user defined function.

If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

Can you post some VB code?|||Find attached the classes we use to build some of the keys we use for address manipulation.

If you know a way to build all these keys into separate fields through SQL I will be very grateful

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 = (SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi
you can use somewhat this
Update
Set A.Field1 = B.Field
From
Where A.id = B.i
This statement this convert to UPDATE CURSOR in Oracle
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 => (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have
to
> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise
Manager
> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi.
you can use somewhat this:
Update A
Set A.Field1 = B.Field1
From B
Where A.id = B.id
This statement this convert to UPDATE CURSOR in Oracle.
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 =
> (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have

---

to

quote:

---

> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise

---

Manager

quote:

---

> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

---

|||Thank you all for your help. If I need more help, I will post a more
complete problem. I am new to "posting" issues, too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Declaring a cursor on a temporary table

How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Personcan i ask especially what your trying to do more in detail please.|||I hjave NEVER done this...and only did this for myself to see if it works (didn't see why it wouldn't), but I highly DO NOT recommed this...

USE Northwind
GO
SELECT * INTO #TEMP FROM Orders

DECLARE
@.OrderID int
, @.CustomerID nchar(10)
, @.EmployeeID int
, @.OrderDate datetime
, @.RequiredDate datetime
, @.ShippedDate datetime
, @.ShipVia int
, @.Freight money
, @.ShipName nvarchar(80)
, @.ShipAddress nvarchar(120)
, @.ShipCity nvarchar(30)
, @.ShipRegion nvarchar(30)
, @.ShipPostalCode nvarchar(20)
, @.ShipCountry nvarchar(30)
, @.LoopCounter int

DECLARE myCursor99 CURSOR
FOR
SELECT OrderID
, CustomerID
, EmployeeID
, OrderDate
, RequiredDate
, ShippedDate
, ShipVia
, Freight
, ShipName
, ShipAddress
, ShipCity
, ShipRegion
, ShipPostalCode
, ShipCountry
FROM #Temp

OPEN myCursor99

SELECT @.LoopCounter = 0

FETCH NEXT FROM myCursor99 INTO
@.OrderID
, @.CustomerID
, @.EmployeeID
, @.OrderDate
, @.RequiredDate
, @.ShippedDate
, @.ShipVia
, @.Freight
, @.ShipName
, @.ShipAddress
, @.ShipCity
, @.ShipRegion
, @.ShipPostalCode
, @.ShipCountry

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Some Code
SELECT @.LoopCounter = @.LoopCounter + 1
FETCH NEXT FROM myCursor99 INTO
@.OrderID
, @.CustomerID
, @.EmployeeID
, @.OrderDate
, @.RequiredDate
, @.ShippedDate
, @.ShipVia
, @.Freight
, @.ShipName
, @.ShipAddress
, @.ShipCity
, @.ShipRegion
, @.ShipPostalCode
, @.ShipCountry
END
CLOSE myCursor99
DEALLOCATE myCursor99
DROP TABLE #Temp
SELECT 'Loops incurred: ' + CONVERT(varchar(15),@.LoopCounter)
GO|||I've simplified a stored proc
It's the "declare Age dynamic scroll cursor for select Age from #TempPerson
" that doesn't work

create procedure GetAges
as
begin
declare @.Age char(20),
declare @.PreviousAge char(20),

declare Age dynamic scroll cursor for select Age from #TempPerson

select Name,Age into #TempPerson From Person
insert into #TempPerson (Name,Age) Select LastName,Age From OtherPerson Where TypePerson = '1'

select @.PreviousAge=' '

open Age
while 1=1
begin
fetch next Age into @.Age
if @.Age <> @.PreviousAge
...
select @.Age=@.PreviousAge
close Age
end|||OK Brett thank you -- AGAIN --
that principle will work
I'll give you some news tomorrow
I'm fed up with stored proc for today !!!!!|||Good Luck, but I bet you, if you tell me what you're trying to do, we can find a set based solution...|||I'm migrating the Sybase structure to SQL Server
and I've go to rewrite
Very-Stupidly-And-Badly-Written-Sybase Watcom-Stored proc

I hate working with programs written by spagetti-minded-programmers

So you don't want to see to stored proc (REALLY don't !!!)|||Yup, sometimes it's not an option...

I will go feel bad for you now....

Declare Variable As Table

hi all
how can I Declare Variable As Table Like Cursor
DECLARE parameter_cursor CURSOR FOR
SELECT top 1 c.name, t.name
FROM sysobjects o
JOIN syscolumns c on c.id = o.id
JOIN systypes t on c.xtype = t.xtype
WHERE o.name like 'Test'
order by o.name, c.colid
TanksYou cannot declare a table variable like a cursor, however you can declare a
table variable and then insert the data into the table variable. It will be
available for all the operations within the given scope.
Anith

Declare or Create cursor

Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?
Is Declare Cursor same as Create Cursor and if not what is the major difference?Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

DECLARE CURSOR is part of the T-SQL Language dfinition. CREATE CURSOR is not.|||I do not think there is "Create Cursor". Does anyone know that?

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 for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

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

Deallocating cursor if exception occured.

Hi all,
i am facing one problem while executing stored procedure.
What i am doing is i am opening one cursor and then depending on values
fetch inside the cursor i am inserting data into one table.
But sometimes my insert query failes due to violation in primary key.
In that case sp is not directly stopping its execution.
So my question is if such situation occured how will i deallocate my
cursor. I am using sql server 2000.
How to handle such exception to execute next part of stored procedure.
Thanks in advance.
trialproduct2004@.yahoo.com wrote:
> Hi all,
> i am facing one problem while executing stored procedure.
> What i am doing is i am opening one cursor and then depending on values
> fetch inside the cursor i am inserting data into one table.
>
I suggest you do that with a WHERE clause rather than a cursor.
INSERT INTO tbl1 (co1, col2, ...)
SELECT col1, col2, ...
FROM tbl2
WHERE ... ?

> But sometimes my insert query failes due to violation in primary key.
> In that case sp is not directly stopping its execution.
Ditto. Better to fix your code rather than handle the errors it causes.
Change your INSERT query to avoid inserting duplicate rows.
Cursors should be a last resort only. At least 99.9% of the time you
can and should avoid them.
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/ms130214(en-US,SQL.90).aspx