conditional where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

conditional where clause

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2008-12-21 : 23:53:54

Is it possible to place conditional where clauses? does anyone have a particular case such as this? thank you.

like:


  <SELECT STATEMENT>
  IF <expression = true> then 
       first where clause
       ELSE
       second where clause
  END IF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 00:26:04

Well you can't do that with an IF clause, but you can achieve it by other means. Show us an actual example of what you are trying to do, and we'll show you the efficient query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

j0shua
Starting Member

40 Posts

Posted - 2008-12-22 : 00:32:37

This is my actual stored procedure. As you can see, its too long and i am looking for ways to make it shorter and efficient, thank you i really appreciate your help :)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



--SAMPLE: usp_t_transaction_listByCustomer 'eb5007f0-88c9-4758-83df-5bf2c831dc14','','',''

ALTER    PROC dbo.usp_t_transaction_listByCustomer
	@fxKeyCustomer 	uniqueidentifier,
	@fcType 	varchar(50) = '',
	@fdDateFrom SMALLDATETIME = NULL,
	@fdDateTo SMALLDATETIME = NULL

AS

IF @fdDateTo IS NULL
	SET @fdDateTo=@fdDateFrom

IF @fcType <> ''
BEGIN 
	IF @fcType = 'Sales Orders'
       BEGIN
		IF @fdDateFrom IS NULL OR @fdDateFrom = ''
		   BEGIN
-- 			PRINT 'NULL'
			SELECT 	fxKeySO as fxKey,
				'tSalesOrder' as fcTable,
				'fxKeySO' as fcCol,
				'Sales Order' as fcType,
				CAST(fcSONo as nVarchar) as fcNo,
				fdDateTransact as fdDate,
				'Sales Orders' as fcAccount,
				fdTotal as fdAmount
			FROM 	tSalesOrder
			WHERE	fxKeyCustomer = @fxKeyCustomer
			ORDER BY  fdDate
		   END
          ELSE
		   BEGIN
--   			PRINT 'NOT NULL'
			SELECT 	fxKeySO as fxKey,
				'tSalesOrder' as fcTable,
				'fxKeySO' as fcCol,
				'Sales Order' as fcType,
				CAST(fcSONo as nVarchar) as fcNo,
				fdDateTransact AS fdDate,
				'Sales Orders' as fcAccount,
				fdTotal as fdAmount
			FROM 	tSalesOrder
			WHERE	fxKeyCustomer = @fxKeyCustomer AND fdDateTransact BETWEEN @fdDateFrom AND @fdDateTo
			ORDER BY  fdDate
             END
       END
	ELSE IF @fcType = 'Invoices'
          BEGIN
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
			BEGIN
				SELECT 	fxKeyInvoice as fxKey,
					'tInvoice' as fcTable,
					'fxKeyInvoice' as fcCol,
					'Invoice' as fcType,
					cast(fcInvoiceNo as nVarchar) as fcNo,
					fdDateTransact as fdDate,
					'Accounts Receivable' as fcAccount,
					fdBalance as fdAmount
				FROM 	tInvoice
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 		fbCreditMemo = 0
				ORDER BY 	fdDate
			END
			ELSE
			BEGIN
				SELECT 	fxKeyInvoice as fxKey,
					'tInvoice' as fcTable,
					'fxKeyInvoice' as fcCol,
					'Invoice' as fcType,
					cast(fcInvoiceNo as nVarchar) as fcNo,
					fdDateTransact as fdDate,
					'Accounts Receivable' as fcAccount,
					fdBalance as fdAmount
				FROM 	tInvoice
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 		fbCreditMemo = 0 AND fdDateTransact BETWEEN @fdDateFrom AND @fdDateTo
				ORDER BY 	fdDate
			END
		END
	ELSE IF @fcType = 'Credit Memos'
		BEGIN
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
			BEGIN
				SELECT 	fxKeyCredit as fxKey,
				'tCreditMemo' as fcTable,
				'fxKeyCredit' as fcCol,
				'Credit Memo' as fcType,
				cast(fcCreditNo as nVarchar) as fcNo,
				fdTransDate as fdDate,
				'' as fcAccount,
				fdTotCredit as fdAmount
				FROM 	dbo.tCreditMemo
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 		fbCancelled = 0
				ORDER BY 	fdDate
			END
			ELSE
			BEGIN
				SELECT 	fxKeyCredit as fxKey,
				'tCreditMemo' as fcTable,
				'fxKeyCredit' as fcCol,
				'Credit Memo' as fcType,
				cast(fcCreditNo as nVarchar) as fcNo,
				fdTransDate as fdDate,
				'' as fcAccount,
				fdTotCredit as fdAmount
				FROM 	tCreditMemo
				WHERE	fxKeyCustomer = @fxKeyCustomer
			 	AND 		fdTransDate BETWEEN @fdDateFrom AND @fdDateTo
				ORDER BY 	fdDate
			END
		END
	ELSE IF @fcType = 'Debit Memos'
		BEGIN
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
			BEGIN
				SELECT 	fxKeyDebit as fxKey,
				'tDebitMemo' as fcTable,
				'fxKeyDebit' as fcCol,
				'Debit Memo' as fcType,
				cast(fcDebitNo as nVarchar) as fcNo,
				fdDateTrans as fdDate,
				'' as fcAccount,
				fdDBTol as fdAmount
				FROM 	dbo.tDebitMemo
				WHERE	fxKeyName = @fxKeyCustomer
				ORDER BY 	fdDate
			END
			ELSE
			BEGIN
				SELECT 	fxKeyDebit as fxKey,
				'tDebitMemo' as fcTable,
				'fxKeyDebit' as fcCol,
				'Debit Memo' as fcType,
				cast(fcDebitNo as nVarchar) as fcNo,
				fdDateTrans as fdDate,
				'' as fcAccount,
				fdDBTol as fdAmount
				FROM 	tDebitMemo
				WHERE	fxKeyName = @fxKeyCustomer
				AND 		fdDateTrans BETWEEN @fdDateFrom AND @fdDateTo
				ORDER BY 	fdDate
			END
		END

	ELSE IF @fcType = 'Journal Entries'
		begin
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
				begin
					SELECT 	fxKeyJVNo as fxKey,
						'tJVEntry' as fcTable,
						'fxKeyJVNo' as fcCol,
						'Journal Entry' as fcType,
						cast(right(left(fiEntryNo,6),4)+
						right(left(fiEntryNo, 9),2)+
						left(right(fiEntryNo, 8),2)+
			 			right(fiEntryNo,5) as nVarchar) as fcNo,
						fdTransDate AS fdDate,
						'Journal Entries' AS fcAccount,
						fdTotAmt as fdAmount
					FROM dbo.tJVEntry
					WHERE fxKeyNameID = @fxKeyCustomer
					ORDER BY fdDate
				end
			else
				begin
					select 	fxKeyJVNo as fxKey,
						'tJVEntry' as fcTable,
						'fxKeyJVNo' as fcCol,
						'Journal Entry' as fcType,
						cast(right(left(fiEntryNo,6),4)+
						right(left(fiEntryNo, 9),2)+
						left(right(fiEntryNo, 8),2)+
			 			right(fiEntryNo,5) as nVarchar) as fcNo,
						fdTransDate AS fdDate,
						'Journal Entries' AS fcAccount,
						fdTotAmt AS fdAmount
					FROM dbo.tJVEntry
					WHERE fxKeyNameID = @fxKeyCustomer
					  AND fdTransDate BETWEEN @fdDateFrom AND @fdDateTo
					ORDER BY fdDate
				end
		end

	ELSE IF @fcType = 'Sales Receipts'
		BEGIN
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
			BEGIN
				SELECT 	fxKeySR as fxKey,
					'tSalesReceipt' as fcTable,
					'fxKeySR' as fcCol,
					'Sales Receipt' as fcType,
					cast(fcSaleNo as nVarchar) as fcNo,
					fdDateTransact AS fdDate,
					'Undeposited Funds' AS fcAccount,
					fdBalanceDue AS fdAmount
				FROM tSalesReceipt
				WHERE	fxKeyCustomer = @fxKeyCustomer
				ORDER BY 	fdDate
				
			END
			ELSE
			BEGIN
				SELECT 	fxKeySR as fxKey,
					'tSalesReceipt' as fcTable,
					'fxKeySR' as fcCol,
					'Sales Receipt' as fcType,
					cast(fcSaleNo as nVarchar) as fcNo,
					fdDateTransact AS fdDate,
					'Undeposited Funds' AS fcAccount,
					fdBalanceDue AS fdAmount
				FROM tSalesReceipt
				WHERE fxKeyCustomer = @fxKeyCustomer AND fdDateTransact BETWEEN @fdDateFrom AND @fdDateTo
				ORDER BY fdDate
			END
		END
	ELSE IF @fcType = 'Received Payments'
		BEGIN
			IF @fdDateFrom IS NULL OR @fdDateFrom = ''
			BEGIN
				SELECT 	fxKeyPayment as fxKey,
					'tPayment' as fcTable,
					'fxKeyPayment' as fcCol,
					'Payment' as fcType,
					cast(fcRefNo as nVarchar) as fcNo,
					fdDate,
					'Undeposited Funds' AS fcAccount,
					fdPayment AS fdAmount
				FROM 	tPayment
				WHERE	fxKeyCustomer = @fxKeyCustomer
				ORDER BY 	fdDate
			END
			ELSE
			BEGIN
				SELECT 	fxKeyPayment as fxKey,
					'tPayment' as fcTable,
					'fxKeyPayment' as fcCol,
					'Payment' as fcType,
					cast(fcRefNo as nVarchar) as fcNo,
					fdDate,
					'Undeposited Funds'AS fcAccount,
					fdPayment AS fdAmount
				FROM 	tPayment
				WHERE	fxKeyCustomer = @fxKeyCustomer AND fdDate BETWEEN @fdDateFrom AND @fdDateTo
				ORDER BY 	fdDate
			END
		END
END
ELSE
BEGIN
	IF @fdDateFrom IS NULL OR @fdDateFrom = ''
		BEGIN
				SELECT 	fxKey,
						fcTable,
						fcCol,
						fcType,
						fcNo,
						fdDate,
						fcAccount,
						fdAmount
				 FROM
				(SELECT 	fxKeyInvoice as fxKey,
					'tInvoice' as fcTable,
					'fxKeyInvoice' as fcCol,
					'Invoice' as fcType,
					fcInvoiceNo as fcNo,
					fdDateTransact as fdDate,
					'Accounts Receivable' as fcAccount,
					fdBalance as fdAmount
				FROM 	tInvoice
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 	fbCreditMemo = 0
			
				UNION
			
				SELECT 	fxKeyCredit as fxKey,
					'tCreditMemo' as fcTable,
					'fxKeyCredit' as fcCol,
					'Credit Memo' as fcType,
					fcCreditNo as fcNo,
					fdTransDate as fdDate,
					'' as fcAccount,
					fdTotCredit as fdAmount
					FROM 	dbo.tCreditMemo
					WHERE	fxKeyCustomer = @fxKeyCustomer
					AND 	fbCancelled = 0
			
				UNION
			
				SELECT 	fxKeyJVNo as fxKey,
					'tJVEntry' as fcTable,
					'fxKeyJVNo' as fcCol,
					'Journal Entry' as fcType,
					cast(right(left(fiEntryNo,6),4)+
					right(left(fiEntryNo, 9),2)+
					left(right(fiEntryNo, 8),2)+
					 right(fiEntryNo,5) as nVarchar) as fcNo,
					fdTransDate as fdDate,
					'Journal Entries' as fcAccount,
					fdTotAmt as fdAmount
					from dbo.tJVEntry
					where fxKeyNameID = @fxKeyCustomer
			
				UNION
			
				SELECT 	fxKeyDebit as fxKey,
					'tDebitMemo' as fcTable,
					'fxKeyDebit' as fcCol,
					'Debit Memo' as fcType,
					fcDebitNo as fcNo,
					fdDateTrans as fdDate,
					'' as fcAccount,
					fdDBTol as fdAmount
					FROM 	dbo.tDebitMemo
					WHERE	fxKeyName = @fxKeyCustomer
							
				UNION
				
				SELECT 	fxKeyPayment as fxKey,
					'tPayment' as fcTable,
					'fxKeyPayment' as fcCol,
					'Payment' as fcType,
					fcRefNo as fcNo,
					fdDate,
					'Undeposited Funds' as fcAccount,
					fdPayment as fdAmount
				FROM 	tPayment
				WHERE	fxKeyCustomer = @fxKeyCustomer
				
				UNION
				
				SELECT 	fxKeySO as fxKey,
					'tSalesOrder' as fcTable,
					'fxKeySO' as fcCol,
					'Sales Order' as fcType,
					fcSONo as fcNo,
					fdDateTransact AS fdDate,
					'Sales Orders' as fcAccount,
					fdTotal as fdAmount
				FROM 	tSalesOrder
				WHERE	fxKeyCustomer = @fxKeyCustomer
				
				UNION
				
				SELECT 	fxKeySR as fxKey,
					'tSalesReceipt' as fcTable,
					'fxKeySR' as fcCol,
					'Sales Receipt' as fcType,
					fcSaleNo as fcNo,
					fdDateTransact AS fdDate,
					'Undeposited Funds'as fcAccount,
					fdBalanceDue as fdAmount
				FROM 	tSalesReceipt
				WHERE	fxKeyCustomer = @fxKeyCustomer
				
				)q ORDER by fcType, fdDate
		END

		ELSE

		BEGIN
				SELECT 	fxKey,
						fcTable,
						fcCol,
						fcType,
						fcNo,
						fdDate,
						fcAccount,
						fdAmount
				FROM
				(SELECT 	fxKeyInvoice as fxKey,
					'tInvoice' as fcTable,
					'fxKeyInvoice' as fcCol,
					'Invoice' as fcType,
					fcInvoiceNo as fcNo,
					fdDateTransact as fdDate,
					'Accounts Receivable' as fcAccount,
					fdBalance as fdAmount
				FROM 	tInvoice
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 	fbCreditMemo = 0 
							
				UNION
			
				SELECT 	fxKeyCredit as fxKey,
					'tCreditMemo' as fcTable,
					'fxKeyCredit' as fcCol,
					'Credit Memo' as fcType,
					fcCreditNo as fcNo,
					fdTransDate as fdDate,
					'' as fcAccount,
					fdTotCredit as fdAmount
				FROM 	dbo.tCreditMemo
				WHERE	fxKeyCustomer = @fxKeyCustomer
				AND 	fbCancelled = 0
							
				UNION
			
				SELECT 	fxKeyJVNo as fxKey,
					'tJVEntry' as fcTable,
					'fxKeyJVNo' as fcCol,
					'Journal Entry' as fcType,
					cast(right(left(fiEntryNo,6),4)+
					right(left(fiEntryNo, 9),2)+
					left(right(fiEntryNo, 8),2)+
					right(fiEntryNo,5) as nVarchar) as fcNo,
					fdTransDate as fdDate,
					'Journal Entries' as fcAccount,
					fdTotAmt as fdAmount
				FROM dbo.tJVEntry
				WHERE fxKeyNameID = @fxKeyCustomer
							
				UNION
			
				SELECT 	fxKeyDebit as fxKey,
					'tDebitMemo' as fcTable,
					'fxKeyDebit' as fcCol,
					'Debit Memo' as fcType,
					fcDebitNo as fcNo,
					fdDateTrans as fdDate,
					'' as fcAccount,
					fdDBTol as fdAmount
				FROM dbo.tDebitMemo
				WHERE fxKeyName = @fxKeyCustomer
											
				UNION
				
				SELECT 	fxKeyPayment as fxKey,
					'tPayment' as fcTable,
					'fxKeyPayment' as fcCol,
					'Payment' as fcType,
					fcRefNo as fcNo,
					fdDate,
					'Undeposited Funds' as fcAccount,
					fdPayment as fdAmount
				FROM tPayment
				WHERE fxKeyCustomer = @fxKeyCustomer
								
				UNION
				
				SELECT 	fxKeySO as fxKey,
					'tSalesOrder' as fcTable,
					'fxKeySO' as fcCol,
					'Sales Order' as fcType,
					fcSONo as fcNo ,
					fdDateTransact as fdDate,
					'Sales Orders' as fcAccount,
					fdTotal as fdAmount
				FROM tSalesOrder
				WHERE fxKeyCustomer = @fxKeyCustomer
								
				UNION
				
				SELECT 	fxKeySR as fxKey,
					'tSalesReceipt' as fcTable,
					'fxKeySR' as fcCol,
					'Sales Receipt' as fcType,
					fcSaleNo as fcNo,
					fdDateTransact as fdDate,
					'Undeposited Funds' as fcAccount,
					fdBalanceDue as fdAmount
				FROM tSalesReceipt
				WHERE fxKeyCustomer = @fxKeyCustomer
				
				
				)q WHERE fdDate BETWEEN @fdDateFrom AND @fdDateTo 
					 ORDER by fcType, fdDate
		END

	
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 04:00:28

www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources