Alter my stored procedure in effectively

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Alter my stored procedure in effectively

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-04 : 05:46:06

Hi all,

I'm asp.net developer and i have handling all the sql errors and transactions rollback in my front end coding. But i want to handle this from sql server itself.

My stored procedure is like the below

CREATE PROCEDURE SP_SPTest 
	-- Add the parameters for the stored procedure here
	@Name Varchar(50), @Address Varchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO Test (FirstName, Address) VALUES (@Name, @Address)
	    
END

Kindly include TRANSACTION-- ROLLBACk---COMMIT block and RAISE ERROR block in the above SP.

Pls send me soon,

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 06:11:13

Follow this

http://blog.sqlauthority.com/2007/10/03/sql-server-2005-explanation-of-try%E2%80%A6catch-and-error-handling-with-raiseerror-function/

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-04 : 06:52:12

Thanks senthil.. I gone thru the Try..catch block. I want Transaction and commit block in sp. pls sned me

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 07:30:18

Try like this

/*

exec sample 0

*/

Create procedure sample
@var_ip int
as
Begin

Begin Tran

select 100/@var_ip as val

If @@error>0
Rollback
else
Commit

End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-04 : 07:49:12

thnx senthil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources