Error - Conversion failed when converting the varchar value

Ramco Ramco

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

ALTER PROCEDURE [dbo].[MaxDocEntry]
	-- Add the parameters for the stored procedure here
      @TableName VARCHAR(50) 
	  ,@DocEntry INT OUTPUT
	  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET @DocEntry = 'SELECT MAX(docentry) FROM ' + @TableName
END

Thanks

2 Replies

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

Naimish Makwana2y ago
Accepted answer
To ensure that @DocEntry is set to 1 if there are no records in the table, you can modify your stored procedure to handle this case. You can use the ISNULL function to set a default value if the MAX(docentry) returns NULL.

Here’s the updated stored procedure:
```
ALTER PROCEDURE [dbo].[MaxDocEntry]
    @TableName VARCHAR(50),
    @DocEntry INT OUTPUT
AS
BEGIN
    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;
END
```
In this version:
1. The ISNULL function is used to return 0 if MAX(docentry) is NULL.
2. Adding 1 to the result ensures that @DocEntry is set to 1 if there are no records.
This way, if the table is empty, @DocEntry will be set to 1. If there are records, it will be set to the maximum docentry value plus 1.

Thanks
Naimish Makwana2y ago
The error you’re encountering is because you’re trying to assign a string to an integer variable. To fix this, you need to execute the dynamic SQL and then assign the result to the @DocEntry variable. Here’s how you can modify your stored procedure:
```
ALTER PROCEDURE [dbo].[MaxDocEntry]
    @TableName VARCHAR(50),
    @DocEntry INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

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

    SET @SQL = 'SELECT @Result = MAX(docentry) FROM ' + @TableName;

    EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT;

    SET @DocEntry = @Result;
END
```
In this version:
1. The dynamic SQL is constructed and stored in the @SQL variable.
2. The sp_executesql system stored procedure is used to execute the dynamic SQL and capture the result in the @Result variable.
3. Finally, the result is assigned to the @DocEntry output parameter.
This should resolve the conversion error you’re experiencing.

Thanks