Hello all,
This Article explains how to create a Stored procedure in sql server 2005 with Exception handling.
Old way was to use if else condition with @@error and raiserror Method
But the sql server 2005 has new way to handle Exception using try catch block...
Hey
this code is also important in case of maintaining Transaction In sql server.
if in a stored procedure there is more than one Insert queries , which insert the incoming data in more than one table then transaction has to be maintained.
If transaction is not maintained then Half of the information is inserted and half information is not inserted in database .
That is all the data is inserted in table whose insert quires arrive before the Query which throw exception and all insert quries below that error quiry is not fired and Transaction failed .
whole database structure and constaint failure.
in sql server transaction is maintained By
RollBack and Commit
Thus the New code in Sql server:
CraetePROCEDURE [dbo].[proc_LoginCreateUser]
(
@emailid nvarchar(max)
,@password nvarchar(max)
,@firstname nvarchar(100)
,@lastname nvarchar(100)
,@jobtitle nvarchar(max)
,@company nvarchar(max)
,@phoneno nvarchar(50)
,@intrests nvarchar(max)
)
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
declare @CheckemailID nvarchar(max)
-- to get the Current password of that user by loginid(session)
SELECT @CheckemailID=Email_ID
FROM [Login_UserDetails]
where Email_ID = @emailid
IF (@CheckemailID <> @emailid or @CheckemailID is null )
BEGIN
--insert a new record in "Login_UserDetails" to create a new user
--note: Login Id is Auto incremented so no use to insert it...
insert into dbo.Login_UserDetails
(UserID
,Email_ID
,Password
,FirstName
,LastName
,JobTitle
,Company
,Phone
,Interests
,UserVisits
,CreatedDate
,ModifiedDate
,IsActive)
values(NEWID()
,@emailid
,@password
,@firstname
,@lastname
,@jobtitle
,@company
,@phoneno
,@intrests
,1
,getdate()
,getdate()
,null)
Select 'Right'
END
ELSE
BEGIN
Select 'Wrong'
END
-- This Means all the thing is fine and now commit this transaction
COMMIT
END TRY
BEGIN CATCH
-- There was a exception occured somewhere and please roll back
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
This is the actual way insert / update query must be written in Sqlserver 2005
