Returning a single character value from a SQL stored procedure using C#.

            try
            {
                NextRankDataSet = new System.Data.DataSet();          /* Define the data set for the list of assignments */
                NextRankDataSet.CaseSensitive = false;

                NextRankDataCommand = new System.Data.SqlClient.SqlCommand();
                NextRankDataCommand.Connection = GPDataConnection;

                NextRankDataCommand.CommandText =
                    "SELECT CONCAT(REPLICATE('0'," + ChrCount.ToString() + "-LEN(CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1)))),CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1))) AS NEXTRANK" +
                    "FROM [APDSDATA].[dbo].[PERSASGN] " +
                    "WHERE [GRUPFLAG] = '" + GroupFlag + "'";

                NextRankDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                NextRankDataAdapter.SelectCommand = NextRankDataCommand;
                _commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(NextRankDataAdapter);

                NextRankDataAdapter.Fill(NextRankDataSet);

                txtRankNumber.Text = NextRankDataSet.Tables[0].Rows[0]["NEXTRANK"].ToString();
            }

Select allOpen in new window

/****** Object:  StoredProcedure [dbo].[GetNextRank]    Script Date: 1/29/2019 5:09:51 PM ******/
DROP PROCEDURE [dbo].[GetNextRank]
GO

/****** Object:  StoredProcedure [dbo].[GetNextRank]    Script Date: 1/29/2019 5:09:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================================
-- Author:		Richard E. Wheeler - Revered Business Solutions, Inc.
-- Create date: 02/10/2019
-- Description:	Determine the next highest ranking of personnel within each group
-- @I_CHRCOUNT 2 for 2 digit rank numbers and 3 for 3 digit rank numbers
-- ==========================================================================
CREATE PROCEDURE [dbo].[GetNextRank]
	@I_GRUPFLAG NVARCHAR(50),
	@I_CHRCOUNT INT,
	@I_NEXTRANK NVARCHAR(50)
		
AS
BEGIN

	SET NOCOUNT ON

DECLARE @Err_Msg NVARCHAR(4000)
DECLARE @ErrSeverity INT
DECLARE @ErrCode INT

/* If an active transaction cannot be committed, roll it back */
IF XACT_STATE() = -1
	ROLLBACK TRANSACTION
ELSE
/* If an active transaction(s) can be committed, commit it(them). */
IF XACT_STATE() = 1
	WHILE @@TRANCOUNT > 0
		COMMIT TRANSACTION

BEGIN TRANSACTION

BEGIN TRY

	SELECT @I_NEXTRANK = CONCAT(REPLICATE('0',@I_CHRCOUNT-LEN(CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1)))),CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1))) 
	FROM [APDSDATA].[dbo].[PERSASGN]
	WHERE [GRUPFLAG]=@I_GRUPFLAG

	SELECT @I_NEXTRANK

	SET @ErrCode = 0

END TRY
BEGIN CATCH
	SELECT @Err_Msg = ERROR_MESSAGE(),
					@ErrSeverity = ERROR_SEVERITY();
	RAISERROR(@Err_Msg,@ErrSeverity,1)

	SET @ErrCode = 1
END CATCH

-- Complete the transaction
IF XACT_STATE() = 1
	COMMIT TRANSACTION
ELSE
IF XACT_STATE() = -1
	ROLLBACK TRANSACTION

RETURN @ErrCode
END



GO

Select allOpen in new window