Close cursor in another procedure (part II)

<I'm sorry for new post but I couldn't reply to group because of errors>
Hi David,
I have a lot of reasons to using cursor and I don't know better way to do
this.
For example;
I control datas in the cursor like this:
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
SELECT @.BLABLA = BLABLA FROM BLABLA
IF @.MyVariable < @.BLABLA THEN
BEGIN
RAISEERROR(...)
..
END
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
And this is insert script: "INSERT INTO TableA(...) SELECT ... FROM xxx"
Better idea?CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
AS
IF EXISTS
(SELECT *
FROM blabla AS B
JOIN Inserted AS I
ON I.myfield < B.blabla
WHERE ... /* ' unspecified */)
BEGIN
EXEC my_error_sp
RAISERROR ...
END
GO
David Portas
SQL Server MVP
--|||Thanks David,
Let me ask last question.
I have a log system like this:
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
DECLARE @.RESULT BIGINT
DECLARE @.TABLE_NAME VARCHAR(128)
DECLARE @.OBJ_ID INT
SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
//if log inserted successful then do other operations else rollback and
exit
IF @.RESULT = -1
BEGIN
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor
DEALLOCATE MyCursor
RETURN
END
DoSomething
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
END
--
DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
...
INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
IF @.@.ERROR <> 0
RETURN -1
...
--
How can I do without any cursor?|||See if this helps:
How do I audit changes to SQL Server data?
http://www.aspfaq.com/show.asp?id=2448
AMB
"Tod" wrote:

> Thanks David,
> Let me ask last question.
> I have a log system like this:
> --
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> DECLARE @.RESULT BIGINT
> DECLARE @.TABLE_NAME VARCHAR(128)
> DECLARE @.OBJ_ID INT
> SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
> SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
> //if log inserted successful then do other operations else rollback and
> exit
> IF @.RESULT = -1
> BEGIN
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor
> DEALLOCATE MyCursor
> RETURN
> END
> DoSomething
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> END
> --
> DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
> ...
> INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
> IF @.@.ERROR <> 0
> RETURN -1
> ...
> --
> How can I do without any cursor?
>
>|||The problem here is that you apparently want to call an SP for each row
in the INSERT. In itself, that is a weak concept for writing modular
data-manipulation code. You should aim to write set-based stored procs
that operate on SETS of data rather than one row at a time (except for
code supporting the UI where you often want to support single row
updates). Everything you want still can be possible without a cursor
but you may need to adjust the logic of your SpLog code. Since you
haven't told us what it does, I can't show you how to do it.
Also, note that if you need common code in triggers you can generate
the code semi-automatically from the metadata which reduces the need
for code re-use through procs. In general you want to minimize calling
other procs from a trigger.
David Portas
SQL Server MVP
--

Close cursor in another procedure

Hi,
I declared a cursor in an Insert trigger. In this trigger I called a sp. Can
I close this cursor in the sp?
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
CREATE MY_ERROR_SP AS
...
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
...
----Why have you put a cursor in a trigger? Cursors are rarely a good idea
and triggers are absolutely the last place you should use them. Updates
are set-based so triggers should be too.
The answer to your question is no, because your cursor is local. The
better answer is rewrite your trigger.
David Portas
SQL Server MVP
--|||Cursors by default hv global scope. Therefore, it is possible to create them
in 1 SP1 & close it in SP2 called from SP1.
Rakesh
"Tod" wrote:

> Hi,
> I declared a cursor in an Insert trigger. In this trigger I called a sp. C
an
> I close this cursor in the sp?
> ----
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> ...
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> ...
> END
> ...
> ----
> CREATE MY_ERROR_SP AS
> ...
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
> DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
> ...
> ----
>
>|||David is right.. since ur cursor is a local one, u will not be able to close
.
"Rakesh" wrote:
> Cursors by default hv global scope. Therefore, it is possible to create th
em
> in 1 SP1 & close it in SP2 called from SP1.
> Rakesh
> "Tod" wrote:
>

Client Side Cursor vs Sever Side Cursor?

I having a difficult time here trying to figure out what to do here.
I need a way to scroll through a recordset and display the results
with both forward and backward movement on a web page(PHP using
ADO/COM)..

I know that if I use a client side cursor all the records get shoved
to the client everytime that stored procedure is executed..if this
database grows big wont that be an issue?..

I know that I can set up a server side cursor that will only send the
record I need to the front end but..

Ive been reading around and a lot of people have been saying never to
use a server side cursor because of peformance issues.

So i guess im weighing network performance needs with the client side
cursor vs server performance with the server side cursor..I am really
confused..which one should I use?

-JimJim:

> I having a difficult time here trying to figure out what to do here.
> I need a way to scroll through a recordset and display the results
> with both forward and backward movement on a web page(PHP using
> ADO/COM)..

Not sure why you would need anything else other than a forward only
recordset on a website. Unless you are planning on persisting the recordset
for the session and then navigating back and forth thru it.

> I know that if I use a client side cursor all the records get shoved
> to the client everytime that stored procedure is executed..if this
> database grows big wont that be an issue?..

Please note that your "Client" in this case would be the web server and not
the actual end users browser.

> Ive been reading around and a lot of people have been saying never to
> use a server side cursor because of peformance issues.

Does your web server (IIS or Apache) reside on the same physical server as
the database?. I would think that the performance issues would come from the
network trip hit. But if the web server and database are on the same
physical server, then this wouldn't matter.

Personally, I don't like the idea of returning 1,000,000 records just to
display 20 at a time. Nor do I think you can keep a connection/recordset
alive in between web pages without severaly affecting scalability.

You may want to do a search on this newsgroup thru google groups for other
paging techniques that will return only the results the user needs.

HTH,

BZ

"Jim" <jim.ferris@.motorola.com> wrote in message
news:729757f9.0312181931.4e4bba84@.posting.google.c om...
> I having a difficult time here trying to figure out what to do here.
> I need a way to scroll through a recordset and display the results
> with both forward and backward movement on a web page(PHP using
> ADO/COM)..
> I know that if I use a client side cursor all the records get shoved
> to the client everytime that stored procedure is executed..if this
> database grows big wont that be an issue?..
> I know that I can set up a server side cursor that will only send the
> record I need to the front end but..
> Ive been reading around and a lot of people have been saying never to
> use a server side cursor because of peformance issues.
> So i guess im weighing network performance needs with the client side
> cursor vs server performance with the server side cursor..I am really
> confused..which one should I use?
> -Jim

Client Side Cursor and CommitTrans

I am trying to trap an error using VB6, ADO 2.8 and SQL Server 2000. Using
a client side cursor, if I pull the ethernet cable just before issuing a
CommitTrans statement, the CommitTrans succeeds anyway. I cannot get the
Errors collection, or WithEvents to report any problem. What now..'I might consider taking this over to the .vb groups to see what they say.
We mostly talk T-SQL specifically around here.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Scott Meloney" <scottm1100@.yahoo.com> wrote in message
news:%230wkWcQpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>I am trying to trap an error using VB6, ADO 2.8 and SQL Server 2000. Using
>a client side cursor, if I pull the ethernet cable just before issuing a
>CommitTrans statement, the CommitTrans succeeds anyway. I cannot get the
>Errors collection, or WithEvents to report any problem. What now..'
>