Dynamic reports sql procedure.

Sau đây là thủ tục sql động để tìm kiếm nhiều điều kiện cùng lúc.

CREATE PROCEDURE [dbo].[spTest]
 @nhan_hieu VARCHAR(30) = null,
 @kieu VARCHAR(30) = null,
 @tu_ngay VARCHAR (19) = null,
 @den_ngay VARCHAR (19) = null 
    AS
    BEGIN

 DECLARE @SQL varchar(1000) 
 SET @SQL = 'SELECT nhan_hieuName, kieu, CAST(kieu_dang AS decimal(10,1)) AS [kieu_dang], loai, nhan_hieu, DATENAME(Month, DATEADD(Month, DATEPART(Month, Date), 0) - 1) AS [thang], SUM(gia) AS [Tong ban ra], COUNT(*) AS [Tong ] FROM [products] '

 IF (@nhan_hieu IS NOT NULL AND @nhan_hieu != '') OR (@kieu IS NOT NULL AND @kieu != '') OR (@tu_ngay IS NOT NULL) OR (@den_ngay IS NOT NULL)
  SET @SQL = @SQL + 'WHERE ' 

 IF @nhan_hieu IS NOT NULL AND @nhan_hieu != '' 
  SET @SQL = @SQL + 'nhan_hieuName = ' + '''' + @nhan_hieu + ''''


 IF @kieu IS NOT NULL AND @kieu != '' 
  SET @SQL = @SQL + 'and kieu = ' + '''' + @kieu + ''''

 IF @tu_ngay IS NOT NULL
  SET @SQL = @SQL + ' AND [Date] >= ' + '''' + @tu_ngay + ''''

 IF @den_ngay IS NOT NULL 
  SET @SQL = @SQL + ' AND [Date] <= ' + '''' +  @den_ngay + ''''

 SET @SQL = @SQL + 'GROUP BY DATEPART(m, Date), nhan_hieuName, kieu, kieu_dang, loai, nhan_hieu '
 SET @SQL = @SQL + 'ORDER BY nhan_hieuName, kieu, DATEPART(m, Date), kieu_dang, loai, nhan_hieu'
 print(@sql)
 ---EXEC(@SQL)
    
    END