Procedure Case Falscher Syntax naehe ','?

Question

Hallo,

ich haenge leider seit einiger Zeit an einem Problem:

	
ALTER PROCEDURE dbo.SelComStandard

	(
		@iCategory TINYINT,
		@bVault BIT,
		@bPhotos BIT,
		@iSort TINYINT,
		@iFilter TINYINT,
		@sFilter NVARCHAR(255)
	)	
	
AS

	SELECT
			idCom,
			bVault,
			iCategory,
			sCommission,
			iNetplanNo,
			iOrderNo,
			sAdvisor,
			sConstructor,
			sComment,
			iMetres,
			dSapTerm,
			dDtgTerm,
			dCustomer,
			dPhotos,
			bCanceled,
			bF3Present,
			bPhotos,
			dRecorded
	
	FROM dbo.tblCommission
	
	WHERE (iCategory=@iCategory)
	
	AND (bVault=@bVault)
	AND (bPhotos=@bPhotos)
	
	AND 
		CASE WHEN @iFilter=1 THEN sCommission END IS NOT NULL,
		CASE WHEN @iFilter=2 THEN dPhotos END IS NOT NULL,
		CASE WHEN @iFilter=3 THEN dDtgTerm END IS NOT NULL,
		CASE WHEN @iFilter=4 THEN dSapTerm END IS NOT NULL,
		CASE WHEN @iFilter=5 THEN sCommission END LIKE '%' + @sFilter + '%',
		CASE WHEN @iFilter=6 THEN sComment END LIKE '%' + @sFilter + '%',
		CASE WHEN @iFilter=7 THEN dCustomer END IS NOT NULL
			
	ORDER BY
		CASE WHEN @iSort=1 THEN sCommission END,
		CASE WHEN @iSort=2 THEN dDtgTerm END,
		CASE WHEN @iSort=3 THEN dSapTerm END,
		CASE WHEN @iSort=4 THEN dPhotos END,
		CASE WHEN @iSort=5 THEN dCustomer END,
		CASE WHEN @iSort=101 THEN sCommission END DESC,
		CASE WHEN @iSort=102 THEN dDtgTerm END DESC,
		CASE WHEN @iSort=103 THEN dSapTerm END DESC,
		CASE WHEN @iSort=104 THEN dPhotos END DESC,
		CASE WHEN @iSort=105 THEN dCustomer END DESC
		        
RETURN

 Ist es nicht moeglich nach END IS NOT NULL oder LIKE xxx anzuhaengen, denn man kann ja auch wie bei iSort zb. DESC anhaengen?

Oder hab ich einen Denkfehler? MS SQL Server 2008 r2

Diese Methode hat zb funktioniert, aber dann muss ich LIKE anders verarbeiten:

CASE @iFilter
		WHEN 1 THEN sCommission
		WHEN 2 THEN dPhotos
		WHEN 3 THEN dDtgTerm
		WHEN 4 THEN dSapTerm
		WHEN 7 THEN dCustomer
END

IS NOT NULL

Gruss Sam

Answer 1

Das sieht verdächtig nach einer "Catch-all query" aus. Außerdem besteht die Gefahr des Parameter "Sniffing".

Ich würde das Ganze in ein dynamsiches SQL wandeln.

Answer 2

Danke fuer Deine Antwort, dynamisches SQL wollte ich eigentlich vermeiden. Catch all query / Paramter Sniffing werd ich mir durchlesen. 

Answer 3

Da ist dein Problem

CASE WHEN @iFilter=1 THEN sCommission END IS NOT NULL,
		CASE WHEN @iFilter=2 THEN dPhotos END IS NOT NULL,
		CASE WHEN @iFilter=3 THEN dDtgTerm END IS NOT NULL,
		CASE WHEN @iFilter=4 THEN dSapTerm END IS NOT NULL,
		CASE WHEN @iFilter=5 THEN sCommission END LIKE '%' + @sFilter + '%',
		CASE WHEN @iFilter=6 THEN sComment END LIKE '%' + @sFilter + '%',
		CASE WHEN @iFilter=7 THEN dCustomer END IS NOT NULL

Es sollte eigentlich so aussehen

CASE
 WHEN @iFilter=1 THEN sCommission
 WHEN @iFilter=2 THEN dPhotos
 ...
 WHEN @iFilter=7 THEN dCustomer
END IS NOT NULL

Answer 4

Sorry. Du hast Dir aber mein Posting nicht komplett angesehen? Du beschreibst eine von mir bereits erwaehnte Methode in der ich wie bereits erwaehnt die LIKE Anweisungen nicht verarbeiten kann...

Aber ich denke ich werde, dass ganze Konzept nochmal ueberdenken und anders loesen.

Answer 5

Wenn Du Dir selber mein Posting genauer anschaust :-), wirst du feststellen, dass es nicht genau das ist, was du geschrieben hattest.

Diese Form der CASE Syntax wird Dir erlauben, deine LIKE Anweisung unverändert zu benutzen.

Ich gebe Dir allerdings Recht. Ich hatte das Ende deines Postings etwa vernachlässigt. Sonst hätte ich diesen Punkt betont.

Answer 6

Entschuldige,

versteh ich nicht ganz:

Nehmen wir an iFilter=6

CASE
WHEN @iFilter=1 THEN sCommission
WHEN @iFilter=2 THEN dPhotos
WHEN @iFilter=6 THEN sComment 
WHEN @iFilter=7 THEN dCustomer
END IS NOT NULL

dann ensteht doch:

WHERE sComment IS NOT NULL

und nicht

WHERE sComment LIKE '%' + @sFilter + '%'

jetzt bin ich verwirrt. Aber auf LMU92 Rat werd ich sowieso nochmals alles ueberdenken

Answer 7

Mein Punkt, dass es 2 Syntaxe für die Case Anweisung gibt.

Syntax 1:

CASE Variable
 WHEN Wert1 THEN Ergebnis1
 WHEN Wert2 THEN Ergebnis2
 ...
 WHEN WertN THEN ErgebnisN
 ELSE StandardErgebnis
END

Syntax 2:

CASE Variable
 WHEN Ausdruck1 THEN Ergebnis1
 WHEN Ausdruck2 THEN Ergebnis2
 ...
 WHEN AusdruckN THEN ErgebnisN
 ELSE StandardErgebnis
END

Somit (Syntax 2) sollte folgendes (oder etwas ähnliches) funktionieren:

CASE
 WHEN @iFilter=1 THEN sCommission
 WHEN @iFilter=2 THEN dPhotos
 WHEN @iFilter=3 THEN dDtgTerm
 WHEN @iFilter=4 THEN dSapTerm
 
 WHEN @iFilter=5
  AND sCommission LIKE '%' + @sFilter + '%'
  THEN sCommission
 WHEN @iFilter=6 AND sComment LIKE '%' + @sFilter + '%'
  THEN sComment

 WHEN @iFilter=7 THEN dCustomer
END IS NOT NULL