Issue With Stored Procedure using parameters and IN Clause

Question

I get this error when excuting the procedure. If I hard code the value for @DocStatus then I get the same error for @AppID.

Must declare the scalar variable "@DocStatus"

--EXEC Update_DocTypeStatus 0, 1, '5,6,8'

ALTER PROCEDURE [dbo].[Update_DocTypeStatus]
 @DocStatus bit= NULL,
 @AppID int= NULL,
 @DocNumber varchar= NULL
AS
DECLARE @Returns BIT 
DECLARE @RowCount INTEGER

BEGIN
 SET NOCOUNT ON;
 DECLARE @return_value int

 BEGIN
 exec('UPDATE DocumentType2 SET IsActive = @DocStatus WHERE AppID = @AppID AND DocNumber in ('+ @DocNumber +')')
 if (@@ROWCOUNT = 0)
 SET @Returns = 0
 else
 SET @Returns = 1
 END
 RETURN @Returns
END

Answer 1

Hi @Qamar, Mo ,

You need to convert the int or bit to varchar type inside the sql string. Otherwise you would get the error 'Must declare the scalar variable'.

Please refer below and check whether it is helpful to you:

ALTER PROCEDURE [dbo].[Update_DocTypeStatus]  
     @DocStatus bit,  
     @AppID int,  
     @DocNumber varchar(1000),  
  @Returns BIT OUTPUT  
 AS  
     
 BEGIN  
    SET NOCOUNT ON;  
    DECLARE    @return_value int  
      
 DECLARE @SQL NVARCHAR(MAX)  
  
    SET @SQL ='UPDATE DocumentType2 SET IsActive = '+CAST(@DocStatus AS VARCHAR(100))+' WHERE AppID = '+CAST(@AppID  AS VARCHAR(100))+' AND DocNumber in ('+ @DocNumber +')'  
       
  BEGIN  
  EXECUTE sp_executesql  @SQL  
    
         if (@@ROWCOUNT = 0)  
             SET @Returns = 0  
         else  
             SET @Returns = 1  
     END  
     RETURN @Returns  
 END  

Call this procedure:

 DECLARE @count INT;  
  
EXEC Update_DocTypeStatus 0, 1, '5,6,8',@count OUTPUT;  
  
SELECT @count  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

If you are going to use dynamic SQL, you need to use a parameterised statement. However, there is zero reason to use dynamic SQL here. You use a function to split the comma-separated list into a table. I have a short article about it here: http://www.sommarskog.se/arrays-in-sql.html