Member Avatar for Sujit_Patil

Hi,
I am create one application using C#.net and SQL Server 2005,
In this application uses procedure,plz give me syntax how to create procedure in SQL Server and how to handle error in stored procedure.

  • 3 Contributors
  • 2 Replies
  • 124 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

asked for the CREATE PROCEDURE syntax and how to handle errors. The simplest, reliable pattern for SQL Server 2005 is: set up the procedure with SET NOCOUNT ON, use explicit transactions for multi-statement work, wrap the logic in BEGIN TRY ... END TRY / BEGIN CATCH ... END CATCH, and surface errors with RAISERROR while returning an integer status or populating an OUTPUT parameter. RETURN only returns an integer; use OUTPUT parameters (NVARCHAR for messages) when a text message is needed.

A compact, typical server-side pattern (SQL Server 2005) looks like this:

CREATE PROCEDURE dbo.DoWork
  @Id INT,
  @Status INT OUTPUT,
  @ErrorMessage NVARCHAR(4000) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  BEGIN TRY
    BEGIN TRAN;
      -- perform DML here
    COMMIT TRAN;
    SET @Status = 0;
    SET @ErrorMessage = NULL;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRAN;
    SET @Status = ERROR_NUMBER();
    SET @ErrorMessage = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
  END CATCH
END

Tie this back to the client: as showed, add a ReturnValue parameter for integer status or an OUTPUT parameter for text. A RAISERROR with severity 11-16 causes a SqlException on the client; catch SqlException in C# and/or read the OUTPUT/ReturnValue after execution. Avoid swallowing exceptions and prefer explicit rollback in the CATCH block.

Notes and cautions: THROW (which preserves original error info more cleanly) is available only in SQL Server 2012+. Use RAISERROR carefully — severities 20+ can terminate connections or require elevated privileges. For official details on TRY/CATCH and RAISERROR, see Microsoft Docs: TRY...CATCH (Transact-SQL) and RAISERROR (Transact-SQL).

Recommended Answers

All 2 Replies

Member Avatar for Renukavani

Use this

SqlConnection myConnection =
          new SqlConnection("Data Source=IPaddress;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password");


SqlCommand cmd = new SqlCommand("SP name", myConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter par = cmd.Parameters.Add("@MESSAGE ", SqlDbType.Varchar);
        par.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("param", SqlDbType.NVarChar, 50).Value = "**";

 myConnection.Open();
            cmd.ExecuteNonQuery();
            string strerrormes=par.Value;

Database side
IF @@ERROR <> 0
SET @MESSAGE ='FAILURE'

RETURN

Edited by Renukavani because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.