Ramco Ramco

Ramco Ramco

  • 442
  • 3.4k
  • 516.8k

error - Conversion failed when converting the varchar value

Sep 2 2024 6:40 AM

Hi

  In below Sp i am getting error - Conversion failed when converting the varchar value 'SELECT MAX(docentry) FROM Department' to data type int.

Docentry is of data type Int.

ALTER PROCEDURE [dbo].[MaxDocEntry]
	-- Add the parameters for the stored procedure here
      @TableName VARCHAR(50) 
	  ,@DocEntry INT OUTPUT
	  ,@Success BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @SQL NVARCHAR(1000);
    DECLARE @Result INT;

	SET @SQL = 'SELECT @Result = ISNULL(MAX(docentry), 0) + 1 FROM ' + @TableName + ' WITH (TABLOCKX)';
    EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT;

    SET @DocEntry = @Result;
	IF @@ROWCOUNT > 0
        SET @Success = 1;
    ELSE
        SET @Success = 0;
END

When i execute below in Sql it works fine.

DECLARE @SQL NVARCHAR(1000);
    DECLARE @Result INT;

	SET @SQL = 'SELECT @Result = ISNULL(MAX(docentry), 0) + 1 FROM ' + 'Department' + ' WITH (TABLOCKX)';
    EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT;
	print @result

Thanks


Answers (1)