July 15, 2012 at 8:53 pm
Can someone help me? I am getting this error:
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 23
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 31
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 39
Incorrect syntax near '@Status'.
=========
With this bit of code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Create_Accounts]
-- Add the INPUT parameters for the stored procedure here
@userid UNIQUEIDENTIFIER
, @IPAddress NVARCHAR(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
-----------------------------------------------
BEGIN TRANSACTION
SAVE TRANSACTION BeforeInserts
-----------------------------------------------
INSERT INTO [dbo].[Create_ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
@status = 1
-----------------------------------------------
INSERT INTO [dbo].[Create_AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
IF @status = 3
BEGIN
COMMIT TRANSACTION
END
ELSE
ROLLBACK TRANSACTION BeforeInserts
END
RETURN
-----------------------------------------------
Error_Create_ContentProviderAccount:
SELECT @ErrorMsg = 'Error_Create_ContentProviderAccount: ' + str(@ErrorNum)
PRINT @ErrorMsg
ROLLBACK TRANSACTION BeforeInserts
RETURN
July 16, 2012 at 12:39 am
Error block is missing for the statement GOTO Error.
July 16, 2012 at 12:55 am
rob.vig (7/15/2012)
Can someone help me? I am getting this error:Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 23
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 31
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 39
Incorrect syntax near '@Status'.
=========
With this bit of code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Create_Accounts]
-- Add the INPUT parameters for the stored procedure here
@userid UNIQUEIDENTIFIER
, @IPAddress NVARCHAR(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
-----------------------------------------------
BEGIN TRANSACTION
SAVE TRANSACTION BeforeInserts
-----------------------------------------------
INSERT INTO [dbo].[Create_ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
@status = 1
-----------------------------------------------
INSERT INTO [dbo].[Create_AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
IF @status = 3
BEGIN
COMMIT TRANSACTION
END
ELSE
ROLLBACK TRANSACTION BeforeInserts
END
RETURN
-----------------------------------------------
Error_Create_ContentProviderAccount:
SELECT @ErrorMsg = 'Error_Create_ContentProviderAccount: ' + str(@ErrorNum)
PRINT @ErrorMsg
ROLLBACK TRANSACTION BeforeInserts
RETURN
You can not assign the variable like C# in SQL 😉
So you can not use
@Status = 1
Use this
SET @status = 1
--OR
SELECT @status = 1
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 16, 2012 at 1:04 am
rhythmk (7/16/2012)
rob.vig (7/15/2012)
Can someone help me? I am getting this error:Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 23
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 31
Incorrect syntax near '@Status'.
Msg 102, Level 15, State 1, Procedure sp_Create_Accounts, Line 39
Incorrect syntax near '@Status'.
=========
With this bit of code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Create_Accounts]
-- Add the INPUT parameters for the stored procedure here
@userid UNIQUEIDENTIFIER
, @IPAddress NVARCHAR(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
-----------------------------------------------
BEGIN TRANSACTION
SAVE TRANSACTION BeforeInserts
-----------------------------------------------
INSERT INTO [dbo].[Create_ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
@status = 1
-----------------------------------------------
INSERT INTO [dbo].[Create_AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
IF @status = 3
BEGIN
COMMIT TRANSACTION
END
ELSE
ROLLBACK TRANSACTION BeforeInserts
END
RETURN
-----------------------------------------------
Error_Create_ContentProviderAccount:
SELECT @ErrorMsg = 'Error_Create_ContentProviderAccount: ' + str(@ErrorNum)
PRINT @ErrorMsg
ROLLBACK TRANSACTION BeforeInserts
RETURN
You can not assign the variable like C# in SQL 😉
So you can not use
@Status = 1Use this
SET @status = 1
--OR
SELECT @status = 1
Also your Error block is missing 😉
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 16, 2012 at 7:02 am
I noticed I named my Error section incorrectly when I was pasting the code.
Here the revised:
Error:
SELECT @ErrorMsg = 'Error: ' + str(@ErrorNum)
PRINT @ErrorMsg
ROLLBACK TRANSACTION BeforeInserts
RETURN
Thank you to all who responded :>
July 16, 2012 at 7:07 am
Why are you using @@Error and goto and not the TRY ... CATCH error handling?
@@Error works, but it's more complex and easier to get wrong than try...catch
p.s BEGIN TRANSACTION immediately followed by SAVE TRANSACTION. Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 8:11 am
Why am I using @@Error? Because this is my first Transact statment and I don't know any better... ;>
This is what I have right now and it is working... Please, do me a favor and re-write it.
========
--sp_Create_Accounts03 - This works
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
GO
ALTER PROCEDURE [dbo].[sp_Create_Accounts]
-- Add the INPUT parameters for the stored procedure here
@userid UNIQUEIDENTIFIER
, @IPAddress NVARCHAR(16)
AS
BEGIN TRANSACTION ProcessSignUp -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
SET @status = 0
-----------------------------------------------
SAVE TRANSACTION BeforeInserts
-----------------------------------------------
INSERT INTO [dbo].[ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
INSERT INTO [dbo].[AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
SELECT @ErrorNum = @@error
IF @ErrorNum <> 0
GOTO Error
ELSE
-----------------------------------------------
IF @status = 3
--BEGIN
COMMIT TRANSACTION ProcessSignUp -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--END
ELSE
ROLLBACK TRANSACTION BeforeInserts
--END
RETURN
-----------------------------------------------
Error:
SELECT @ErrorMsg = 'Error: ' + str(@ErrorNum)
PRINT @ErrorMsg
ROLLBACK TRANSACTION BeforeInserts
RETURN
July 16, 2012 at 8:17 am
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
Also, why the SAVE TRANSACTION? Are you expecting this to be called from a nested transaction? If so, why no checks of @@TranCount? What happens to the open transaction if you do roll back to the savepoint? Why the named transactions?
(btw, if you can't answer the why on those, it's a good indication you shouldn't have that specific statement)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 8:22 am
Why all the "Why's"???
Like I said... this is my first T SQL statement!
I wanted to place 2 INSERT's and one DELETE. If they didn't work, I want to abort the whole thing. I found some code in a book and reworked it.
Normally I'd do this in C# / ADO.NET
Good article! Thanks 10/4
July 16, 2012 at 8:42 am
rob.vig (7/16/2012)
Why all the "Why's"???Like I said... this is my first T SQL statement!
I wanted to place 2 INSERT's and one DELETE. If they didn't work, I want to abort the whole thing. I found some code in a book and reworked it.
Normally I'd do this in C# / ADO.NET
Good article! Thanks 10/4
Gail is asking you why because she is trying to understand the purpose, even if your understanding of what is happening does not match what is REALLY happening. If you put these statements into your query, you must have had a reason or something you are trying to achieve. She is trying to get that reason and if you give it to her, she will be able to suggest a better way or the proper way of doing it.
Jared
CE - Microsoft
July 16, 2012 at 8:44 am
rob.vig (7/16/2012)
Why all the "Why's"???
Because your code is over-complex for what it appears to be doing and I'm not sure if there's a reason for it or not. As it is, that's probably going to do some things you don't want (like leave the transaction open if there is an error, possibly resulting in a full hard drive and an offline database from a full transaction log).
General advice: If you don't understand what a statement does or why you should have it, don't use it. That goes especially for code copied from an article. Afterall, when it breaks, you're the one who'll have to fix it.
2 inserts and a delete, rolling back if any fail.
BEGIN TRANSACTION
BEGIN TRY
Insert ...
Insert ...
Delete...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
That's it. Nothing more is necessary. There's a lot more that can be done, but that's the core.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 8:53 am
Thank you.
SQL Server doesn't let me set break points like Visual Studio. When there an error, the line numbers are usually inaccurate and this makes it pretty hard to debug, compared to VS, hence, a lot of unecessary logic in my statement for the sake of getting it to run.
When I get a little more versed in the Syntax, I'll "trim the fat"... But for this particular episode, I saw code that used @@error etc.
in C#???
try catch finally switch case if if else else... Got it, no problem to set break point and see where it's busting.
Running a T SQL statement in Mgmt Studio and trying to figure out where my logic is flawed with line numbers that don't point to the right line of code, AND with Transact stuff I never really work with? Oi.... Time to hit the books again :>
Like I just said in my last post, I want to make 2 INSERT's and 1 DELETE. If they don't work as a batch, I want to abort the whole thing.
I found some T SQL and reworked it. THat's what I ended up with.
July 16, 2012 at 9:01 am
How would this report the error?
BEGIN TRANSACTION
BEGIN TRY
Insert ...
Insert ...
Delete...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
July 16, 2012 at 9:03 am
rob.vig (7/16/2012)
Like I just said in my last post, I want to make 2 INSERT's and 1 DELETE. If they don't work as a batch, I want to abort the whole thing.I found some T SQL and reworked it. THat's what I ended up with.
I understand, the problem is that your code had a very subtle bug in it that you would likely not have found in basic debugging that would have some very nasty effects on a busy server (up to and including taking the database down when the transaction log filled up)
Debugging SQL in management studio is far easier than debugging SQL in VS. The line numbers are as of the start of the batch, not the code window (batches as designated by GO), double-clicking on an error takes you straight to the line with the error.
You can set breakpoints in T-SQL just like in VS. They're not often the best way to debug, but the feature does exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply