Stored Procedure Parameters

• MuhammadShafiq

  SSCrazy

  Points: 2509

  More actions

  October 30, 2011 at 6:02 am

  #250910

  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

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 30, 2011 at 6:29 am

  #1400860

  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

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]