July 16, 2012 at 9:05 am
rob.vig (7/16/2012)
How would this report the error?
It wouldn't. That's why I said there's a lot more that can be done, but that's the core.
If I writing the code, I'd probably build up a custom error in a string variable (in the Catch block) using Error_Message and the related functions and then use RAISERROR to throw that error back to the calling app.
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 9:12 am
I noticed you mentioned "core"... But I wasn't sure if it might deal with the error aspect.
Thank you so much for getting me up to speed. I wish I could clone myself with all the reading I need to do with LINQ, Entity SQL, Entity Framework, and T SQL.... It never ends
July 16, 2012 at 9:18 am
I ran your simplified statement and it ran fine. No Error reporting though
--sp_Create_Accounts04 -
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
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
SET @status = 0
-----------------------------------------------
BEGIN TRY -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Insert ...
INSERT INTO [dbo].[ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
--Insert ...
INSERT INTO [dbo].[AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
--Delete...
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
COMMIT TRANSACTION
END TRY -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-----------------------------------------------
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
July 16, 2012 at 9:22 am
rob.vig (7/16/2012)
I ran your simplified statement and it ran fine. No Error reporting though--sp_Create_Accounts04 -
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
-----------------------------------------------
DECLARE @ErrorNum INT
DECLARE @ErrorMsg NVARCHAR(100)
DECLARE @status INT
SET @status = 0
-----------------------------------------------
BEGIN TRY -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Insert ...
INSERT INTO [dbo].[ContentProviderAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
--Insert ...
INSERT INTO [dbo].[AdvertiserAccounts] ([UserId], [IPAddress])
VALUES (@UserId, @IPAddress)
--Delete...
DELETE FROM [dbo].[SignUps]
WHERE (UserId = @userid)
COMMIT TRANSACTION
END TRY -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-----------------------------------------------
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Gail told you that was the core code needed. For error reporting you need to add more to the code.
July 16, 2012 at 9:22 am
rob.vig (7/16/2012)
I ran your simplified statement and it ran fine. No Error reporting though
No, there won't be any error reporting. As I said
If I writing the code, I'd probably build up a custom error in a string variable (in the Catch block) using Error_Message and the related functions and then use RAISERROR to throw that error back to the calling app.
p.s. btw, I notice the proc name starts sp_. Do you know that means System procedure and means that SQL checks for the procedure in different places to other names? It's a naming that should be reserved for system procedures (SQL's system procedures) only.
Also, any reason why IP address is Unicode? (allowing for oriental, arabic, etc alphabets)
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 9:24 am
To report an error, you can put this into your catch:
declare @STR VARCHAR(MAX) --set size accordingly
select
ErrorNumber=error_number(),
ErrorSeverity=error_severity(),
ErrorState=error_state(),
ErrorProcedure=error_procedure(),
ErrorLine=error_line(),
ErrorMessage=error_message()
set @STR='whatever error you want to report can be put here'
print @STR
raiserror (@str,1,2) with log --logs the error, you can remove "with log" if you don't want it logged
Jared
CE - Microsoft
July 16, 2012 at 9:46 am
Beautiful! Thank you! I will use it!
July 16, 2012 at 10:00 am
I would recommend no using WITH LOG, except for critical errors. Otherwise just pollutes the SQL error log (which is where it's logged to) and makes it harder for the DBA to find errors that he needs to deal with.
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 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply