Display Validation (Error) messages using Stored Procedure in SQL Server

Hi tsakumar81,

You need to concadinate the previous message with the current error message to get all the messages.

Note: Initialize the @Message variable with empty value.

Refer the below query.

SQL

CREATE PROCEDURE ValidateRequest(
	@ReqName VARCHAR(256),
	@ProdpackageID VARCHAR(16),
	@ReqtAmount VARCHAR(15),
	@Message VARCHAR(200) OUT)
AS
BEGIN
	SET @Message = ''

	IF(@ReqName IS NULL OR RTRIM(LTRIM(@ReqName)) = '')
	BEGIN
		SET @Message += 'Request Name is required. ' ;
	END
	ELSE IF(@ReqName IS NOT NULL)
	BEGIN
		IF LEN(@ReqName) > 255
		BEGIN
			SET @Message += 'Request Name cannot be greater than 255 alphanumeric characters. ' ;
		END
	END

	IF(@ProdpackageID IS NULL OR RTRIM(LTRIM(@ProdpackageID )) = '')
	BEGIN
		SET @Message += 'Product Package ID is required. ' ;
	END
	ELSE IF(@ProdpackageID IS NOT NULL)
	BEGIN
		IF LEN(@ProdpackageID) > 15
		BEGIN
			SET @Message += 'Product Package ID cannot be greater than 15 numeric digits.' ;
		END
	END
	SELECT @Message [Message]
END

Implementation

DECLARE @Message VARCHAR(200)
EXEC ValidateRequest @Reqname = ''
                    ,@ProdpackageID = '' 
                    ,@ReqtAmount = 0
                    ,@Message = ''

Output