error - Conversion failed when converting the varchar value

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

The error you’re encountering is due to the way you’re trying to assign the result of the dynamic SQL query to the @Result variable. The sp_executesql procedure doesn’t directly support assigning values to variables in the calling scope. Instead, you need to use an output parameter in the dynamic SQL.

Here’s a revised version of your stored procedure:

ALTER PROCEDURE [dbo].[MaxDocEntry]
    @TableName VARCHAR(50),
    @DocEntry INT OUTPUT,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(1000);
    DECLARE @ParmDefinition NVARCHAR(500);

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

    EXEC sp_executesql @SQL, @ParmDefinition, @Result = @DocEntry OUTPUT;

    IF @DocEntry IS NOT NULL
        SET @Success = 1;
    ELSE
        SET @Success = 0;
END

In this version:

The @Result variable is replaced with @DocEntry directly in the dynamic SQL.
The parameter definition string @ParmDefinition is used to define the output parameter for sp_executesql.

This should resolve the conversion error and correctly assign the maximum docentry value plus one to the @DocEntry output parameter.

Thanks

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana2y ago
Accepted answer
The error you’re encountering is due to the way you’re trying to assign the result of the dynamic SQL query to the @Result variable. The sp_executesql procedure doesn’t directly support assigning values to variables in the calling scope. Instead, you need to use an output parameter in the dynamic SQL.

Here’s a revised version of your stored procedure:
```
ALTER PROCEDURE [dbo].[MaxDocEntry]
    @TableName VARCHAR(50),
    @DocEntry INT OUTPUT,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(1000);
    DECLARE @ParmDefinition NVARCHAR(500);

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

    EXEC sp_executesql @SQL, @ParmDefinition, @Result = @DocEntry OUTPUT;

    IF @DocEntry IS NOT NULL
        SET @Success = 1;
    ELSE
        SET @Success = 0;
END
```
In this version:
- The @Result variable is replaced with @DocEntry directly in the dynamic SQL.
- The parameter definition string @ParmDefinition is used to define the output parameter for sp_executesql.
This should resolve the conversion error and correctly assign the maximum docentry value plus one to the @DocEntry output parameter.

Thanks