Stored Procedure to make listing and search between to and From date or Start Date End Date

In this article i tried to show you , how we can pass the parameter to a stored procedure. We can pass the order type either Complete or failed, Start date and End Date.

CREATE PROCEDURE [dbo].[OrderListingAndSearch] 
@TypeCode VARCHAR(25),
@CustomerName VARCHAR(50)='',
@DATE_FROM VARCHAR(50)='',
@DATE_TO VARCHAR(50)=''

AS
DECLARE @SQL_STR VARCHAR(1000)
  set @SQL_STR = 'Select * from tblPurchaseOrder O   where OrderStatus='''+ @TypeCode +''''

IF(Len(@CustomerName)>0)
    BEGIN
            set @SQL_STR=@SQL_STR +' and O.CustomerName like ''%' + @CustomerName + '%'''

    END
 IF((Len(@DATE_FROM)>0) and (Len(@DATE_TO)>0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)>= CONVERT(DATETIME,'''+ @DATE_FROM +''')  AND CONVERT(VARCHAR,OrderDate,101) <= CONVERT(DATETIME,'''+@DATE_TO +'''))'
    END
 IF((Len(@DATE_FROM)>0) and (Len(@DATE_TO)=0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)>= CONVERT(DATETIME,'''+ @DATE_FROM +'''))'
    END
    IF((Len(@DATE_FROM)=0) and (Len(@DATE_TO)>0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)<= CONVERT(DATETIME,'''+ @DATE_TO +'''))'
    END
   
  set @SQL_STR=@SQL_STR + ' order by orderid desc'
    exec(@SQL_STR)

 

  Copy Right © InfoA2Z