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