Passing a return value using a MS SQL stored procedure

Often times one needs a specific piece of information returned from a stored procedure. In this case I required the specific number of rows returned for a select statement with a single input value.

The purpose in this case was to support a custom form validation error message that informed the user that a specific contact type (input) could not be deleted since X (rowcount output) records are currently using that particular contact type.

Since contact type is a required field, that type cannot be deleted until all records using that particular type must be re-assigned prior to being able to perform the delete. Providing the actual number of records that are affected by the proposed deletion better informs the user’s next steps. In addition it also serves as a useful return point prior to the actual contact type delete stored procedure.

The complete program can be found at my github repository at https://github.com/mcgolkev/MMSIS-SQL/blob/master/README.md

USE [mmsis]
GO
/ Object: StoredProcedure [dbo].[spGetNumContactsXType] Script Date: 4/28/2020 2:09:12 PM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetNumContactsXType](
@Input varchar(50),
@ret_value int output)
AS
BEGIN
SET NOCOUNT ON;
SELECT @Input
FROM Contact
WHERE ContactType = @Input;
SELECT @ret_value = @@ROWCOUNT;
END