Can I avoid temp tables, etc.

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?
2. Are operations in a stored procedure are treated as a transaction?

Any help will be greatly appreciated.

Background: We need to use temp table to pass recordsets from a nested
stored procedure to a calling stored procedure. Our understanding is
that in this case, we have no choice but to use temp tables. So, we
need to optimize the performance as much as possible. To do this, we
wanted to find out whether operations in a stored procedure are treated
as a transaction. We are using SQL 2000 SP4. I could not find any
answers so I did the following experiment.

Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"SELECT * from Articles" takes a long time (about 40 seconds) to
complete

Before executing the SP, the IsUpdate attribute for all articles is 30.
Then I executed this SP. Before the SP is finished, I end the SP
manually. I checked the IsUpdate attribute again, and found that all
Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
operations did not rollback. I interpret this to mean that the whole SP
is not treated as a transaction.

Then, I did experiment 2 below. This time, I explicitly declared the
transaction.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000

SELECT * from Articles

IF @.@.ERROR <0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Before this second SP, the IsUpdate attribute is 20 (set in the first
experiment). I run this second SP and ended it manually before it
finished. I checked the IsUpdate attributes for all Articles's
(ArticlesId < 80000), but their Isupdate attribute is 50. So the
operation did not rollback either. But we have declared the transaction
explicitly. Does this mean that the SP is still not treated as a
transaction?(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @.@.spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

Quote:

Originally Posted by

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.

--
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|||What are you using SET ANSI_NULLS OFF for?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFIERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.

--
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|||Erland and Alexander,
Thanks a lot. I will experiment with them and report back.|||These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.|||(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-

--
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

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.

can anyone help me with a nested count?

Hi All,

I am trying to do a nested count and cannot seem to get it to work. I do realize I cannot do this within the expression so I have been trying to use the code block (report > report properties > code tab) to do this and I have zero experience with VB.

This is for a PO delivery report. The first expression is just counting how many lines on an individual PO are late divided by total number of lines on the PO. In second expression I would like to count how many POs that have a late line on it.

Here is what I have so far.

Expression 1 with count function (this work properly):
=iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value))))

Code block:
Private Shared count as Integer
count=0
Public Function IncrementCount() As String
count = count +1
IncrementCount = CStr(count)
End Function

Expression 2
=iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value))))

& IIf(iif(Fields!DS_Desired_Recv_Date.Value is nothing,
((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))),
((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value)))) > 0, Code.IncrementCount(), Nothing)

The error:
[rsCompilerErrorInCode] There is an error on line 2 of custom code: [BC30188] Declaration expected.
Build complete -- 1 errors, 0 warnings.

Also "Code.IncrementCount()" is underlined with red zigzag indicating an error.

How do I get the code block to work with expression 2?

Thanks,

Blair

I would try changing this: Private Shared count as Integer

To this: Dim count as New Integer()

I've never used that code block portion, but it seems to me that you are trying to declare variables as part of a class when they aren't really in a class?

There is an error on line 2 of custom code: [BC30188] Declaration expected

This indicates to me that the variable count never truly got declared properly.

|||ok, thanks I will give that a try.