June 11, 2013 at 8:14 am
I want to create a stored procedure to insert data into the database.
If for any reason the insertion fails eg: primary key violation or invalid datatype,
I want my stored procedure to return the relevant error message as output parameter.
storedproc(inputparameters inp1,inp2,inp3,inp4 Outputparameter errorMessage)
(inp1+inp2 is the primary key)
Can some one please help me write such a stored procedure. Thanks!
June 11, 2013 at 8:43 am
Sounds like some try...catch error handling might do the trick.
Check out this link:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 11, 2013 at 9:36 am
thanks.. but it's not very helpful.
If someone can provide an exact sample, would appreciate it!
June 11, 2013 at 9:42 am
Annee (6/11/2013)
thanks.. but it's not very helpful.If someone can provide an exact sample, would appreciate it!
Not sure how much more simple it could be than the examples on that page.
begin try
--do some code stuff here (inserts, delete, update, whatever)
end try
begin catch
--anything that throws an exception in the TRY block will be caught here.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2013 at 9:42 am
Why do you show us what you have tried so far. That way we know where you are at and where you are having difficulties.
June 11, 2013 at 12:18 pm
OK, here is my stored procedure
CREATE PROCEDURE [dbo].[pr_insertAgencyVendorInfo]
( @ClientName nvarchar(15),
@VendorName nvarchar(40),
@TaxID nchar(9),
@ActiveFrom datetime,
@ActiveTo datetime)
AS
insert into AgencySupplierPartner
(ClientName,VendorName,TaxID,ActiveFrom,ActiveTo)
VALUES
(@ClientName,@VendorName,@TaxID,@ActiveFrom,@ActiveTo)
------I want to modify so that I get back errorMessage as output parameter like...
CREATE PROCEDURE [dbo].[pr_insertAgencyVendorInfo]
( @ClientName nvarchar(15),
@VendorName nvarchar(40),
@TaxID nchar(9),
@ActiveFrom datetime,
@ActiveTo datetime,
Output ErrorMsg)
..................................................
...................................................
...................................................
June 11, 2013 at 12:27 pm
So just like the example for BOL.
CREATE PROCEDURE [dbo].[pr_insertAgencyVendorInfo]
( @ClientName nvarchar(15),
@VendorName nvarchar(40),
@TaxID nchar(9),
@ActiveFrom datetime,
@ActiveTo datetime,
@ErrorMsg nvarchar(4000) OUTPUT)
as
begin try
insert into AgencySupplierPartner
(ClientName,VendorName,TaxID,ActiveFrom,ActiveTo)
VALUES
(@ClientName,@VendorName,@TaxID,@ActiveFrom,@ActiveTo)
end try
begin catch
SELECT @ErrorMsg = ERROR_MESSAGE()
end catch
The only difference here is you want to use an output parameter. Now when you call this you will need to include that parameter and specify that it is output.
declare @ClientName nvarchar(15),
@VendorName nvarchar(40),
@TaxID nchar(9),
@ActiveFrom datetime,
@ActiveTo datetime,
@ErrorMsg nvarchar(4000)
--Assuming you have populated the variables with some relevant values.
exec pr_insertAgencyVendorInfo @ClientName, @VendorName, @TaxID, @ActiveFrom, @ActiveTo, @ErrorMsg OUTPUT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2013 at 12:49 pm
What Sean said except that i would provide an output value for both cases so that the output parameter is always set to some legitimate value:
CREATE PROCEDURE [dbo].[pr_insertAgencyVendorInfo]
( @ClientName nvarchar(15),
@VendorName nvarchar(40),
@TaxID nchar(9),
@ActiveFrom datetime,
@ActiveTo datetime,
@ErrorMsg nvarchar(4000) OUTPUT)
as
begin try
insert into AgencySupplierPartner
(ClientName,VendorName,TaxID,ActiveFrom,ActiveTo)
VALUES
(@ClientName,@VendorName,@TaxID,@ActiveFrom,@ActiveTo)
set @ErrorMsg = 'OK'
end try
begin catch
SELECT @ErrorMsg = ERROR_MESSAGE()
end catch
The probability of survival is inversely proportional to the angle of arrival.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply