CODEWITHSUNDEEP
sql-serverdatabasestored-procedures
Durgesh Singh
Durgesh Singh

Reputation: 29

What is the default return type of store procedure give with example?

The following Stored Procedure accepts EmployeeId as a parameter. It checks whether an Employee with the supplied EmployeeId exists in the Employees table of the Northwind database.

What will be the default return type and value?

CREATE PROCEDURE CheckEmployeeId
  @EmployeeId INT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Exists INT

  IF EXISTS(SELECT EmployeeId
                    FROM Employees
                    WHERE EmployeeId = @EmployeeId)
  BEGIN
        SET @Exists = 1
  END
  ELSE
  BEGIN
        SET @Exists = 0
  END

  RETURN @Exists
END

Upvotes: 0

Views: 681

Answers (2)

Janmejay Kumar
Janmejay Kumar

Reputation: 319

Whenever, you execute a procedure, it returns an integer status variable.Usually,zero indicates success and non-zero indicates failure.

Now. its depends on you, what integer value you want to return from procedure to inform your application.

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement.

Suppose you have 

Return code value       Meaning
0                       Employee does not exists.
1                       Employee exists.

Example:-

CREATE PROCEDURE CheckEmployeeId
  @EmployeeId INT
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS(SELECT EmployeeId
                    FROM Employees
                    WHERE EmployeeId = @EmployeeId)
  BEGIN
        RETURN(1)  
  END
  ELSE
  BEGIN
        RETURN(0)  
  END

  RETURN(0)  
END

Upvotes: 1

gbn
gbn

Reputation: 432230

RETURN always returns int from a stored procedure.

The default value can vary if - try to return NULL (get zero and a warning) - you get a SQL Server error where you get a value "10 minus the severity level of the error"

But it's always int

Upvotes: 0

Related Questions