How to give conditions in stored procedure when from date and to date are not manditaory

josmi

SOLVED CLOSED ACTIVE ANSWERED
User: josmi
Posted: on May 02, 2016 02:46 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 2220

If the from date is given then i have to retreive all the values above from date .IF to date is given have to retreive all the values below Todate.else between from and to date how it will make changes

Alter  PROCEDURE uspSearchTransaction
@txtTID varchar(50) = null ,
@txtMID varchar(50) = null,
@txtCardNo varchar(50) = null,
@txtFromDate DateTime = null,
@txtToDate DateTime = null,
@txtAccountNo varchar(50) = null,
@txtFromAmount varchar(50) = null,
@txtToAmount varchar(50) = null,
@ddlStatus varchar(50) = null,
@dllType varchar(50) = null,
@ddlOrderBy varchar(50) = null,
@ddlOrder varchar(50) = null
AS
BEGIN

 Select          POSMerchantMaster.Term_id,
				 POSMerchantMaster.EntityID,
				 POS_TRANSACTION_OUT.Merchant_Name,
				 POS_TRANSACTION_OUT.Card_No,
				 POSMerchantMaster.MerCrAccNo,
				 POS_TRANSACTION_OUT.Auth_Id,
				 POS_TRANSACTION_OUT.Card_Type,
				 POS_TRANSACTION_OUT.Txn_Amount ,
				 POS_TRANSACTION_OUT.Comm_Percentage_Rate,
				 POS_TRANSACTION_OUT.Comm_Amount,
				 clearing_txn.card_type,
				 POS_TRANSACTION_OUT.Uploaded_On,
				 POS_TRANSACTION_OUT.File_Name,
				 POS_TRANSACTION_OUT.Settlement_Flag
				
				FROM	POSMerchantMaster
				JOIN POS_TRANSACTION_OUT
				ON	POSMerchantMaster.EntityID = POS_TRANSACTION_OUT.Entity_Id 
				 JOIN	clearing_txn
				ON	POSMerchantMaster.EntityID  = clearing_txn.Entity_Id 
		 where (POSMerchantMaster.Term_id LIKE '%'+@txtTID+'%') and 
               (POSMerchantMaster.EntityID LIKE '%'+@txtMID+'%') and
			   (POS_TRANSACTION_OUT.Card_No LIKE '%'+@txtCardNo+'%') and
			   (POS_TRANSACTION_OUT.Uploaded_On BETWEEN @txtFromDate AND @txtToDate) and
			   (POS_TRANSACTION_OUT.Txn_Amount BETWEEN @txtFromAmount AND @txtToAmount) and
			   (POS_TRANSACTION_OUT.Settlement_Flag LIKE '%'+@ddlStatus+'%') and
			   (clearing_txn.card_type LIKE '%'+@dllType+'%')
			 ORDER by CASE WHEN @ddlOrder='ASC' THEN @ddlOrderBy END,
                      CASE WHEN @ddlOrder='DESC' THEN @ddlOrderBy END DESC
					  END

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on May 02, 2016 02:46 AM
Report

Hi,

Please refer below code.

SQL

DECLARE @Temp AS TABLE(Id INT,[Date] DATETIME)

INSERT INTO @Temp VALUES(1,'2015-02-02')
INSERT INTO @Temp VALUES(2,'2015-02-05')
INSERT INTO @Temp VALUES(3,'2015-02-20')
INSERT INTO @Temp VALUES(4,'2015-05-08')
INSERT INTO @Temp VALUES(5,'2015-08-10')
INSERT INTO @Temp VALUES(6,'2015-08-22')
INSERT INTO @Temp VALUES(7,'2015-09-25')
INSERT INTO @Temp VALUES(8,'2015-12-03')
INSERT INTO @Temp VALUES(9,'2016-01-09')

SELECT * FROM @Temp

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @ToDate = '2015-05-08'

SELECT * FROM @Temp
WHERE ([Date] >= @FromDate OR @FromDate IS NULL)
AND ([Date] <= @ToDate OR @ToDate IS NULL)

I hope this will help you out.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

How to give conditions in stored procedure when from date and to date are not manditaory

Company

Explore

Follow us

Contact Us