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)
Related Alrticles