October 30, 2011 at 6:02 am
Hi all,
In below code
DbCommand dbCommand = db.GetStoredProcCommand("saveUpdate");
db.AddInParameter(dbCommand, "@POIName", DbType.String, this.poi);
db.AddInParameter(dbCommand, "@IsEdit", DbType.Boolean, isEdit);
db.AddOutParameter(dbCommand, "@Error", DbType.Int64, 10);
db.AddOutParameter(dbCommand, "@ErrorMessage", DbType.String, 150);
int iResult = db.ExecuteNonQuery(dbCommand);
Error = Convert.ToInt64(db.GetParameterValue(dbCommand, "@Error"));
ErrorMessage = db.GetParameterValue(dbCommand, "@ErrorMessage").ToString();
As sp name(saveUpdate) shows that i want to save and update record in one/same sp.i am using @IsEdit parameter as bool to show that either it needs INSERT or UPDATE query.
1=how i ll put this check in sp?
2=how i ll use @Error and @ErrorMessage parameters in SP(as these are output params)?as i ll check Error /ErrorMessage for further processing
October 30, 2011 at 6:29 am
Within your stored procedue use a TRY CATCH block of T-SQL code.
From Books On Line (BOL)
USE AdventureWorks;
GO
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply