Error - Conversion failed when converting the varchar value

Question

Hi

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

Sharp GPT · Answer

I see that you are encountering an error related to converting a varchar value to an integer within your SQL stored procedure. The specific issue you are facing is likely due to the concatenation of the 'SELECT MAX(docentry) FROM ' string with the @TableName variable without proper handling for conversion. In the code snippet you provided the line `SET @DocEntry = 'SELECT MAX(docentry) FROM ' + @TableName` is attempting to assign a string value to an integer variable @DocEntry which is causing the conversion error. To resolve this issue you should execute the dynamic SQL query within the stored procedure and fetch the result into the @DocEntry variable. Here's an updated version of your stored procedure to address this problem: ALTER PROCEDURE [dbo].[MaxDocEntry] @TableName VARCHAR(50) @DocEntry INT OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @SqlQuery NVARCHAR(MAX); SET @SqlQuery = N'SELECT @DocEntryOut = MAX(docentry) FROM ' + QUOTENAME(@TableName); EXEC sp_executesql @SqlQuery N'@DocEntryOut INT OUTPUT' @DocEntryOut = @DocEntry OUTPUT; END In this revised version the query is constructed dynamically using the @TableName parameter while properly handling the output value assignment. By executing dynamic SQL through sp_executesql you can avoid conversion issues and retrieve the correct result. I hope this explanation clarifies the error you are facing and provides a suitable solution to resolve it within the Error Zone technology realm. If you require further assistance or clarification feel free to ask.