locked
How can I give integer value in the where condition in dynamic sql RRS feed

  • Question

  • I  have created a dynamic sql. How can I assign integer variable into where condition in my sql below , please help

    declare
    		    @Employee varchar(MAX) 
    		   ,@Year int
    		   ,@Month int
               
     
     Set @employee='1,2,3'
     set @Year = 2019
     set @Month = 9
    
    DECLARE @QRY VARCHAR(MAX);   
    	 SET @QRY = 'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+@Employee+')
    	  and  LogYear = ''+@Year +''
    	  And LogMonth = +@Month+
    	  order by DepotNo,EmployeeName,AttendanceDate';


    polachan

    Friday, September 20, 2019 3:50 PM

Answers

  • Hi Polachan,

    Please refer below script and it will surely solve your problem

    declare
    		    @Employee varchar(MAX) 
    		   ,@Year int
    		   ,@Month int
               
     
     Set @employee='1,2,3'
     set @Year = 2019
     set @Month = 9
    
    DECLARE @QRY VARCHAR(MAX);   
    	 SET @QRY = 'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+@Employee+')
    	  and  LogYear = '+CAST(@Year as varchar)+ '
    	  And LogMonth = '+CAST(@Month as varchar)+'
    	  order by DepotNo,EmployeeName,AttendanceDate';


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Proposed as answer by Tom Phillips Friday, September 20, 2019 4:01 PM
    • Marked as answer by polachan Sunday, September 22, 2019 7:15 AM
    Friday, September 20, 2019 3:56 PM
  • DECLARE @QRY VARCHAR(MAX);   
    	 SET @QRY = 'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+@Employee+')
    	  and  LogYear = '+Cast(@Year as varchar(4)) +'
    	  And LogMonth = '+ Cast(@Month as varchar(2))+ '
    	  order by DepotNo,EmployeeName,AttendanceDate';
     print @QRY
    
     execute(@QRY)

    • Marked as answer by polachan Sunday, September 22, 2019 7:07 AM
    Friday, September 20, 2019 4:22 PM

All replies

  • Hi Polachan,

    Please refer below script and it will surely solve your problem

    declare
    		    @Employee varchar(MAX) 
    		   ,@Year int
    		   ,@Month int
               
     
     Set @employee='1,2,3'
     set @Year = 2019
     set @Month = 9
    
    DECLARE @QRY VARCHAR(MAX);   
    	 SET @QRY = 'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+@Employee+')
    	  and  LogYear = '+CAST(@Year as varchar)+ '
    	  And LogMonth = '+CAST(@Month as varchar)+'
    	  order by DepotNo,EmployeeName,AttendanceDate';


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Proposed as answer by Tom Phillips Friday, September 20, 2019 4:01 PM
    • Marked as answer by polachan Sunday, September 22, 2019 7:15 AM
    Friday, September 20, 2019 3:56 PM
  • DECLARE @QRY VARCHAR(MAX);   
    	 SET @QRY = 'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+@Employee+')
    	  and  LogYear = '+Cast(@Year as varchar(4)) +'
    	  And LogMonth = '+ Cast(@Month as varchar(2))+ '
    	  order by DepotNo,EmployeeName,AttendanceDate';
     print @QRY
    
     execute(@QRY)

    • Marked as answer by polachan Sunday, September 22, 2019 7:07 AM
    Friday, September 20, 2019 4:22 PM
  • A better option is to avoid string concatenation. In your case you can implement a TVP to pass array type data into your procedure.

    You can use sp_executesql for your sample to make it work.

    declare
     @Employee varchar(200) 
     ,@Year int
     ,@Month int
               
     
     Set @employee='1,2,3'
     set @Year = 2019
     set @Month = 9
    
    DECLARE @QRY NVARCHAR(MAX);   
    	 SET @QRY = N'SELECT * from Empl
    	  WHERE 
    	  EmployeeID in ('+ @Employee +')
    	  and  LogYear =  @Year  
    	  And LogMonth =  @Month 
    	  order by DepotNo,EmployeeName,AttendanceDate';
    
     exec sp_executesql @QRY,N'@Employee varchar(200),@Year int,@Month int',@Employee,@Year,@Month;
     

    Friday, September 20, 2019 4:25 PM
  • DECLARE @QRY NVARCHAR(MAX);         SET @QRY = N'SELECT * from Empl
           WHERE        EmployeeID in ('+ @Employee +')
           and  LogYear =  @Year         And LogMonth =  @Month        order by DepotNo,EmployeeName,AttendanceDate';

     exec sp_executesql @QRY,N'@Employee varchar(200),@Year int,@Month int',@Employee,@Year,@Month;

    This is better written as:

    DECLARE @QRY NVARCHAR(MAX);       SET @QRY = N'SELECT * from Empl
         WHERE      EmployeeID in (SELECT value from string_split(@Employee, '',''))
         and  LogYear =  @Year       And LogMonth =  @Month      order by DepotNo,EmployeeName,AttendanceDate';

     exec sp_executesql @QRY,N'@Employee varchar(200),@Year int,@Month int',@Employee,@Year,@Month;

    string_split requires SQL 2016 or later. On earlier versions, you need to use a user-defined string-splitter. Some can be found here:
    http://www.sommarskog.se/arrays-in-sql.html

    I like to stress that this is how you always should pass values to dynamic SQL; you should never build the values into the query string. For one thing, that is a lot more complicated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:51 PM