Setting parameters within a Case expression

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

SQL Server 2005 Forums

Transact-SQL (2005)

Setting parameters within a Case expression

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-08 : 09:22:22

Hi,

I'm trying to set a few additional clauses to the where section of a Select, depending on the type of profile selected but I'm getting a syntax error (Incorrect syntax near '=') when checking my code. Does anyone know what I'm doing incorrectly or what is missing? Here's a sample of my code for two profiles:


DECLARE 
		@FromDate DATETIME,
		@ToDate   DATETIME,
		@Currency VARCHAR(5),
		@Profile  VARCHAR (50),
		@Where	  INT,
		@Where1	  VARCHAR (500),
		@Where2   VARCHAR (7999),
		@Where3   VARCHAR (500),
		@Where4   VARCHAR (500)


SET @FromDate = '2010-10-01 00:00:00'
SET @ToDate   = '2010-10-05 23:59:59'
SET @Profile  = '10-BAC-3RD-GBP'

SELECT CASE
	WHEN @Profile = '10-BAC-3RD-GBP' THEN
		  @Where1 = ' AND fdTradingPartne.TPType NOT IN  (''10'', ''13'', ''14'', ''23'', ''50'')' 
		  @Where3 = ' AND (fgSrcDoc.OpenAmt > 0.0 OR fgSrcDoc.OpenAmt < 4999.0)' 
		  @Where4 = ' AND fdBnkCodSub.BankValue IS NULL' 
	
	WHEN @Profile = '10-BAC-3RD-GBP-ANW' THEN
		  @Where1 = ' AND fdTradingPartne.TPType NOT IN  (''10'', ''13'', ''14'', ''23'', ''50'', ''23A'')' 
		  @Where3 = ' AND fgSrcDoc.OpenAmt > 0.0'
		  @Where4 = ' AND fdBnkCodSub .BankValue = ''MPP'''
END

thank you in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 09:38:28

You can't assign a value to a variable inside the CASE expression.
Try this instead:

DECLARE 
		@FromDate DATETIME,
		@ToDate   DATETIME,
		@Currency VARCHAR(5),
		@Profile  VARCHAR (50),
		@Where	  INT,
		@Where1	  VARCHAR (500),
		@Where2   VARCHAR (7999),
		@Where3   VARCHAR (500),
		@Where4   VARCHAR (500)


SET @FromDate = '2010-10-01 00:00:00'
SET @ToDate   = '2010-10-05 23:59:59'
SET @Profile  = '10-BAC-3RD-GBP'

IF @Profile = '10-BAC-3RD-GBP'
BEGIN
	SET	  @Where1 = ' AND fdTradingPartne.TPType NOT IN  (''10'', ''13'', ''14'', ''23'', ''50'')' 
	SET	  @Where3 = ' AND (fgSrcDoc.OpenAmt > 0.0 OR fgSrcDoc.OpenAmt < 4999.0)' 
	SET	  @Where4 = ' AND fdBnkCodSub.BankValue IS NULL' 
END

IF @Profile = '10-BAC-3RD-GBP-ANW'
BEGIN
	SET	  @Where1 = ' AND fdTradingPartne.TPType NOT IN  (''10'', ''13'', ''14'', ''23'', ''50'', ''23A'')' 
	SET	  @Where3 = ' AND fgSrcDoc.OpenAmt > 0.0'
	SET	  @Where4 = ' AND fdBnkCodSub .BankValue = ''MPP'''
END