May 30, 2007 at 11:43 pm
Hi,
In a stored procedure, i'm looping (with a cursor) on a resultset and for each I'm creating some Insert which I'm using in an Execute command.
Example :
-- Loop on the cursor (I'm not that sure of the while code ... not on my computer ...)
WHILE @@FECTH_STATUS <> 0
-- Creating the string
SET @STR = "INSERT INTO [......] INSERT INTO [....] INSERT INTO [....]
-- Execute
EXECUTE @STR
--
IF @@ERROR <> 0
BEGIN
GOTO ROLL_BACK_TRANSACTION
NEXT
-- [...]
COMMIT_TRANSACTION:
PRINT 'COMMIT DE LA TRANSACTION'
COMMIT TRANSACTION
GOTO END_TRANSACTION
ROLLBACK_TRANSACTION:
PRINT 'ANNULATION DE LA TRANSACTION'
ROLLBACK TRANSACTION
GOTO END_TRANSACTION
END_TRANSACTION:
CLOSE CUR_REQUETE
DEALLOCATE CUR_REQUETE
As you can see I'm using the @@ERROR in order to see if there is a problem on my dynamic text.
The problem is in some case INSERT raise error but @@ERROR still contains 0 ... so the transaction is not rollback ...
So i'm looking for a solution to get info when one of the INSERT raise an error ...
Thanks !
May 31, 2007 at 1:48 am
Execute only returns status information for the last command presented to it. You need to execute each insert statement separately, testing @@error after each.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
May 31, 2007 at 11:23 am
Ouch !!!!
There is no way to know if there is an error in the Execute ?
May 31, 2007 at 5:11 pm
Yes, but not for dynamic SQL...
The error that you are getting is becaue you are using EXEC the wrong way to execute dynamic SQL... you have...
EXECUTE @STR
... and it should be...
EXECUTE (@STR)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2007 at 12:34 am
I think i've try with Execute() anf get the same result ...
But I'm gone try right now :o) !
June 1, 2007 at 2:01 am
So 'Iv trye this
DECLARE @STR AS VARCHAR(8000)
SET @STR = ' INSERT INTO T_B(UID, NAME) VALUES(''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''TEST'')'
SET @STR = @STR + ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
SET @STR = @STR + ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
SET @STR = @STR + ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''E220B491-BBB2-44E4-9481-FA0157946A02'', ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'') '
SET @STR = @STR + ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
SET @STR = @STR + ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
SET @STR = @STR + ' DELETE FROM T_A WHERE UID_B = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
SET @STR = @STR + ' DELETE FROM T_B WHERE UID = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
EXECUTE(@STR)
PRINT @@ERROR
The first two INSERT raise error as they not respect a unique constraint,
The third on raise error as it doesn't respect a FK constraint,
The other request are OK.
The result for the print is 0.
June 1, 2007 at 2:27 am
At the risk of repeating myself
"Execute only returns status information for the last command presented to it. You need to execute each insert statement separately, testing @@error after each."
Try
DECLARE @STR AS VARCHAR(8000)
SET @STR = ' INSERT INTO T_B(UID, NAME) VALUES(''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''TEST'')'
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''E220B491-BBB2-44E4-9481-FA0157946A02'', ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'') '
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' INSERT INTO T_A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' DELETE FROM T_A WHERE UID_B = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
EXECUTE(@STR)
PRINT @@ERROR
SET @STR = ' DELETE FROM T_B WHERE UID = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
EXECUTE(@STR)
PRINT @@ERROR
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 5:17 am
I understant what you wrote and I test : it is working but it's not working in my situation...
As I said, I execute a string that contains many request. Unlike the example I'm not building the string but the string is send to me. There is no way for to split the string I received to do what you write.
The only way would be to say to the one who send me the string to include directly the test which will increase the size of the string and reduce performance (network bandwith : from 128 kbit/s to 1 Mbit/s and many users ...)
That's why I'm just looking for a solution to know if one request of an execute failed ...
June 1, 2007 at 6:00 am
Apologies, now I understand. I thought you were building the strings.
You could separate the commands. Look at http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/ for some clever ways of separating a string and putting the results in a table.
Then use a cursor to iterate over the table containing the commands and execute each one individually.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 6:01 am
Be careful and never disappoint the one who sends strings to you.
Because being in bad mood he could send something like 'DROP DATABASE YourDBName'.
And it would be executed without errors. You may be sure - performance would be great.
_____________
Code for TallyGenerator
June 1, 2007 at 6:08 am
Wise words indeed. A little validation may be desireable.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 6:24 am
<<There is no way for to split the string I received to do what you write.>>
There's always a way...
DECLARE @STR AS VARCHAR(8000), @substr VARCHAR(500), @Statement CHAR(6), @EndPos INT
SET @STR = ' INSERT INTO T_1B(UID, NAME) VALUES(''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''TEST'')'
SET @STR = @STR + ' INSERT INTO T_2A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
SET @STR = @STR + ' INSERT INTO T_3A(UID, UID_B, UID_C) VALUES (newid(), ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
SET @STR = @STR + ' INSERT INTO T_4A(UID, UID_B, UID_C) VALUES (newid(), ''E220B491-BBB2-44E4-9481-FA0157946A02'', ''C7C86B92-EA59-4E4C-B7A5-499F69799C8C'') '
SET @STR = @STR + ' INSERT INTO T_5A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''AFB38D28-7EA3-43C6-92B8-83EB9BE71505'') '
SET @STR = @STR + ' INSERT INTO T_6A(UID, UID_B, UID_C) VALUES (newid(), ''9c96812e-6210-4a4f-af6f-7027fc0f229f'', ''E220B491-BBB2-44E4-9481-FA0157946A02'') '
SET @STR = @STR + ' DELETE FROM T_7A WHERE UID_B = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
SET @STR = @STR + ' DELETE FROM T_8B WHERE UID = ''9c96812e-6210-4a4f-af6f-7027fc0f229f'''
SET @STR = LTRIM(RTRIM(@STR))
WHILE LEN(@STR) > 6
BEGIN
-- What's the first statement in the string?
SELECT TOP 1 @Statement = Statement, @EndPos = StartPos - 1
FROM (SELECT 'INSERT' AS Statement, CHARINDEX('INSERT', UPPER(@STR), 3) AS StartPos
UNION ALL SELECT 'UPDATE' AS Statement, CHARINDEX('UPDATE', UPPER(@STR), 3) AS StartPos
UNION ALL SELECT 'DELETE' AS Statement, CHARINDEX('DELETE', UPPER(@STR), 3) AS StartPos) t
WHERE StartPos > 0 ORDER BY StartPos
SET @substr = SUBSTRING(@STR, 1, @EndPos) SELECT @substr AS 'Substring' -- EXEC the excised statement here
IF NOT @EndPos < LEN(@STR) BREAK -- easier than accounting for unknown number of spaces between statements in the string
-- remove the executed statement from the string and send it round the loop again SET @STR = LTRIM(SUBSTRING(@STR, @EndPos, LEN(@STR)-(@EndPos-1)))
END
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 1, 2007 at 6:46 am
Serqiy's comment set me thinking. Why are you letting someone else send you a SQL string instead of a list of values?
Can you not accept a string like the following (split on different lines for readibility)?
I, T_B, '9c96812e-6210-4a4f-af6f-7027fc0f229f', 'TEST';
I, T_A, 'C7C86B92-EA59-4E4C-B7A5-499F69799C8C', 'AFB38D28-7EA3-43C6-92B8-83EB9BE71505';
I, T_A, 'C7C86B92-EA59-4E4C-B7A5-499F69799C8C', 'E220B491-BBB2-44E4-9481-FA0157946A02') ';
I, T_A, 'E220B491-BBB2-44E4-9481-FA0157946A02', 'C7C86B92-EA59-4E4C-B7A5-499F69799C8C'') ';
I, T_A, '9c96812e-6210-4a4f-af6f-7027fc0f229f', 'AFB38D28-7EA3-43C6-92B8-83EB9BE71505') ';
I, T_A, '9c96812e-6210-4a4f-af6f-7027fc0f229f', 'E220B491-BBB2-44E4-9481-FA0157946A02') ';
D, T_A, '9c96812e-6210-4a4f-af6f-7027fc0f229f';
D, T_B, '9c96812e-6210-4a4f-af6f-7027fc0f229f';
Use the routines I or Chris suggested above to break up the string, then build the SQL yourself. A little validation would be useful here too.
The I/D in the first position indicate Insert or delete.
The suggestion above should reconstitute to your example above; your actual needs may be different.
This will save you bandwith too, as you don't need to send all the SQL verbosity over the network.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 9:50 am
I think I found : SET XACT_ABORT ON.
From the doc, it seems that is stop any transaction running on the first error !
June 1, 2007 at 2:37 pm
Did you try it?
When I used it on your example above it did indeed stop everything in its tracks
- but didn't even print @@error.
Serqiy's comment about the dangers of letting someone feed you SQL still stands.
To me, this is an extremely strong reason why you should rework your code.
The example I presented above will do the job without causing too much disturbance in your existing solution.
It's not the way I'd approach the problem, but it would work.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply