can cursor be nested?

Hi, I'm a newbie on using cursor. My question is if I create an update
trigger that loop through the Inserted rows by a cursor, and inside the
cursor loop, a stored procedure is executed, which contains a cursor loop
too, any problem about it?YEs of course, you can also nest cursors inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
both access the same table, will deadlock occur?
"Jens Sü?meyer" wrote:

> YEs of course, you can also nest cursors inline.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
>
>|||Have a look at the Cursor option in BOL, you can handle the outside cusors
to behave as readonly if you need to. Rember that the default locking
beahviour is row locking, so even you will lock the data with anyother
option it depends on the the option wheter you lock one or multiple
datarows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
> Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
> both access the same table, will deadlock occur?
> "Jens Smeyer" wrote:
>|||another question:
if I open a cursor in a transaction and the transaction rollback, will the
cursor be automatically closed and deallocated?
"Jens Sü?meyer" wrote:

> Have a look at the Cursor option in BOL, you can handle the outside cusors
> to behave as readonly if you need to. Rember that the default locking
> beahviour is row locking, so even you will lock the data with anyother
> option it depends on the the option wheter you lock one or multiple
> datarows.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
>
>|||Because of the termination of the session inthat case, that would be the
effect.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||So to keep up your questions, it depends...
The inner Cursor will be closed when the session ends, the session ends when
the whole logic block is executed or an serverity error occured that kept
SQl Server from continuing the Cursor and the Transaction is ended, that the
fact if you call an procedure in the outer cursor which build up a cursor i
the prcedure)
Its easy to recode if you just write a simple cursor which call a procedure
and this sp establish a cursor which run into an error, try to declare the
cursor with the same name now from the QA.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||To add to the responses by Jens, it is often possible to use a set-based
processing rather than cursors. The set-based approach usually provides
better performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||But why would anyone write code like that in SQL? It is a XXXXX to
maintain, proprietary and each cursor is 1 to 2 orders of magnitude
slower than declarative SQL.
Post the DDL and a statement of the problem and you can get a better
answer.

calling the same store procedure repeatly, but only work in the first time

I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong?

My original version that calls a store procedure

Public

SharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim mySqlCommandAsNew SqlCommandDim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim myPart_idAsString

mySqlCommand.CommandType = CommandType.StoredProcedure

mySqlCommand.CommandText = "getPartId"

mySqlCommand.Connection = mySqlConnection

mySqlCommand.Parameters.Add(

New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

My Store procedure

create procedure getPartId
@.part_supplierserialnumber as nvarchar(50)
as

select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @.part_supplierserialnumber order by part_datecreated desc

GO

The new version I tried which happen the same thing

PublicSharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim myPart_idAsStringDim strSqlAsString = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc"Dim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim mySqlCommandAsNew SqlCommand(strSql, mySqlConnection)Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

I tested your stored procedure. It works.

Here is my test code on the page load event. It retrieves partid for the for loop.

--code

Dim mystring1, mystring2 As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim a(3) As String
a(0) = "abc"
a(1) = "xyz"
a(2) = "hij"
Dim i As Integer = 0
For i = 0 To 2
mystring1 = Convert.ToString(getPartId(a(i)))
mystring2 = mystring2 + mystring1 + "+"
Label1.Text = mystring2

Next
End Sub

--code

But your new version should work with the parameterized query. Not this one with misplaced single quotas. It didn't run.

This one works:

.....

Dim strSql As String = "SELECT top 1 part_id FROM tblPtSingapore where part_supplierserialnumber=@.part_supplierserialnumber order by part_datecreated desc"
Dim mySqlConnection As SqlConnection = New SqlConnection(connString)
Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)
mySqlCommand.Parameters.Add(New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))

......

Check your test data and the for loop too.

|||

I have check the for loop and test data, it seems all rite.. I tried your query too..

I have try calling that store procedure getPartId from another store procedure, but it just doesn't assign the variable with the part id I got from the getPartId, though it does return a part id if I call getPartId in the sql query analyzer.

CREATE PROCEDURE PRVtoRIP
@.part_supplierserialnumber NVARCHAR(30)
AS

declare @.newpart_id as nvarchar(35)
/* get the unique PK for the record, cos at application lvl doesn't work */
Execute @.newpart_id = getPartId @.part_supplierserialnumber

UPDATE tblPtSingapore
SET part_status = 'RIP',
part_datemodified = getDate()
WHERE part_id = @.newpart_id
and part_status = 'PRV'

select @.newpart_id as new_part_id
GO

Calling the same report in a batch (rs2005) from aspx page

Hi,
what would be the best way to call the same report with diffrent
parameters.
right now I am calling the RenderInvoice which is called in a loop
(view code below)
is there a better way to do this, like a batch process?
this works, and it does 50 reports in about one minute, I wonder if I
can make it go faster.
or it's just an issue of computer hardware.
Thanks.
protected Byte[] RenderInvoice(ReportExecutionService rs,string
shipmentId)
{
ParameterValue[] parameters = new ParameterValue[2];
parameters[0] = new ReportExecutionWS.ParameterValue();
parameters[0].Name = "ShipmentId";
parameters[0].Value = shipmentId;
parameters[1] = new ReportExecutionWS.ParameterValue();
parameters[1].Name = "ShowLogo";
parameters[1].Value = "True";
// set report parameters
rs.SetExecutionParameters(parameters, "en-us");
// set rendering info
Byte[] result = null;
String format = "PDF";
String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></
DeviceInfo>";
String extension = "";
String encoding = "";
String mimeType = "";
Warning[] warnings = null;
string[] streamIDs = null;
result = rs.Render(format, devInfo, out extension, out
encoding, out mimeType, out warnings, out streamIDs);
return result;
}You are running the reports one after another. A minute for 50 reports
sounds pretty good to me. RS could do these in parallel but your app would
need to do request the rendering asynchronously. My suggestion is to leave
it alone. I don't think a whole lot of complexity to speed it up more is
worth the effort.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rperetz@.gmail.com> wrote in message
news:1182807919.166551.199320@.g4g2000hsf.googlegroups.com...
> Hi,
> what would be the best way to call the same report with diffrent
> parameters.
> right now I am calling the RenderInvoice which is called in a loop
> (view code below)
> is there a better way to do this, like a batch process?
> this works, and it does 50 reports in about one minute, I wonder if I
> can make it go faster.
> or it's just an issue of computer hardware.
> Thanks.
>
> protected Byte[] RenderInvoice(ReportExecutionService rs,string
> shipmentId)
> {
> ParameterValue[] parameters = new ParameterValue[2];
> parameters[0] = new ReportExecutionWS.ParameterValue();
> parameters[0].Name = "ShipmentId";
> parameters[0].Value = shipmentId;
> parameters[1] = new ReportExecutionWS.ParameterValue();
> parameters[1].Name = "ShowLogo";
> parameters[1].Value = "True";
> // set report parameters
> rs.SetExecutionParameters(parameters, "en-us");
> // set rendering info
> Byte[] result = null;
> String format = "PDF";
> String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></
> DeviceInfo>";
> String extension = "";
> String encoding = "";
> String mimeType = "";
> Warning[] warnings = null;
> string[] streamIDs = null;
>
> result = rs.Render(format, devInfo, out extension, out
> encoding, out mimeType, out warnings, out streamIDs);
> return result;
> }
>