help with case syntax in where clause

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 2000 Forums

Transact-SQL (2000)

help with case syntax in where clause

Author

Topic

missy
Starting Member

4 Posts

Posted - 2006-03-15 : 02:05:38

Hi,

I'm not sure if what I'm writing is T-SQL, but i'll ask and try my luck here...i want to pass an operator as a parameter into my stored procedure. e.g.

CREATE PROCEDURE proc

@id INT,
@operator VARCHAR(5),
@value INT

AS

SELECT * FROM table
WHERE id=@id AND value @operator @value

GO

but since i can't do this, i want to do some case statements but I'm getting the syntax wrong...everything i try doesn't pass syntax checking.

Here's one attempt:

CREATE PROCEDURE proc

@id INT,
@operator VARCHAR(5),
@value INT

AS

SELECT * FROM table
WHERE id=@id AND
CASE WHEN @operator='=' THEN value=@value
WHEN @operator='>' THEN value>@value
END

GO

Can someone correct my syntax for me? or give me a better solution to what I'm trying to do?

Thanks in advance!

melissa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 02:22:39

Why do you have this requirement?
you can use if statement


If @operator='=' 
    SELECT * FROM table
    WHERE id=@id AND value=@value
else if @operator='>' 
    SELECT * FROM table
    WHERE id=@id AND value>@value

Madhivanan

Failing to plan is Planning to fail

missy
Starting Member

4 Posts

Posted - 2006-03-15 : 02:35:37

Hi, thanks for replying...

i wanted to be able to have an operator as input to allow the user to choose what they want to display...

and my select statement is quite long thats why i wanted to use case statements instead...

i did try your suggestion with the if statements just then, but i still get errors. it says error near the IF....

i'm actually editing someone else's stored procedure so i dont know whats going on...here's my code.....

CREATE PROCEDURE [dbo].[proc_myiu_GetReport_Rows] 

@ReportID		INT,
@SuppressColIdx	INT = null,
@operator		VARCHAR(5) = null

AS

SELECT 
	R.[ReportID], 
	R.RowIdx, 
	R.RowName, 
	R.RowDepth

FROM 	dbo.[FS_Report_Rows] R
	

WHERE R.ReportID=@ReportID

	AND (CASE WHEN (@SuppressColIdx IS NOT NULL)
		THEN
			CASE WHEN R.RowIdx  NOT IN (
				IF @operator = '='
					SELECT R1.[RowIdx]
					FROM dbo.[FS_Report_Rows] R1 
					INNER JOIN dbo.[FS_Report_Cells] C1 ON R1.[ReportID] = C1.[ReportID] AND R1.[RowIdx] = C1.[RowIdx]

					WHERE R1.ReportID = @ReportID
					AND C1.Value = 0
					AND C1.[ColIdx] = @SuppressColIdx
				ELSE IF @operator='>'
					SELECT R1.[RowIdx]
					FROM dbo.[FS_Report_Rows] R1 
					INNER JOIN dbo.[FS_Report_Cells] C1 ON R1.[ReportID] = C1.[ReportID] AND R1.[RowIdx] = C1.[RowIdx]

					WHERE R1.ReportID = @ReportID
					AND C1.Value > 0
					AND C1.[ColIdx] = @SuppressColIdx
				ELSE
					SELECT R1.[RowIdx]
					FROM dbo.[FS_Report_Rows] R1 
					INNER JOIN dbo.[FS_Report_Cells] C1 ON R1.[ReportID] = C1.[ReportID] AND R1.[RowIdx] = C1.[RowIdx]

					WHERE R1.ReportID = @ReportID
					AND C1.Value = 0
					AND C1.[ColIdx] = @SuppressColIdx

					

			) THEN 1
			ELSE 0
			END
		ELSE 1
		END) =1

ORDER BY 	R.[RowIdx] ASC
GO

thanks for helping! really appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 03:34:40

Try something like



IF @operator = '='
SELECT 
	R.[ReportID], 
	R.RowIdx, 
	R.RowName, 
	R.RowDepth

FROM 	dbo.[FS_Report_Rows] R
	

WHERE R.ReportID=@ReportID

	AND (CASE WHEN (@SuppressColIdx IS NOT NULL)
		THEN
			CASE WHEN R.RowIdx  NOT IN 
				(
				
					SELECT R1.[RowIdx]
					FROM dbo.[FS_Report_Rows] R1 
					INNER JOIN dbo.[FS_Report_Cells] C1 ON R1.[ReportID] = C1.[ReportID] AND R1.[RowIdx] = C1.[RowIdx]

					WHERE R1.ReportID = @ReportID
					AND C1.Value = 0
					AND C1.[ColIdx] = @SuppressColIdx					

				) THEN 1
				ELSE 0
			END
		ELSE 1
		END) 
ORDER BY 	R.[RowIdx] ASC

else IF @operator = '>'
SELECT 
	R.[ReportID], 
	R.RowIdx, 
	R.RowName, 
	R.RowDepth

FROM 	dbo.[FS_Report_Rows] R
	

WHERE R.ReportID=@ReportID

	AND (CASE WHEN (@SuppressColIdx IS NOT NULL)
		THEN
			CASE WHEN R.RowIdx  NOT IN 
				(
				
					SELECT R1.[RowIdx]
					FROM dbo.[FS_Report_Rows] R1 
					INNER JOIN dbo.[FS_Report_Cells] C1 ON R1.[ReportID] = C1.[ReportID] AND R1.[RowIdx] = C1.[RowIdx]

					WHERE R1.ReportID = @ReportID
					AND C1.Value > 0
					AND C1.[ColIdx] = @SuppressColIdx					

				) THEN 1
				ELSE 0
			END
		ELSE 1
		END) 
ORDER BY 	R.[RowIdx] ASC

Otherwise Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

missy
Starting Member

4 Posts

Posted - 2006-03-15 : 05:52:36

thanks for your help! i will try your solution tomorrow when i get to work...=)

missy
Starting Member

4 Posts

Posted - 2006-03-15 : 17:26:13

Thanks Madhivanan. That worked.

Now my stored procedure is 257 lines!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 01:31:27

>>Now my stored procedure is 257 lines!

Number of lines doesnt matter
To avoid complexity, you need to split the code

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 01:52:20

quote:
Originally posted by missy

Thanks Madhivanan. That worked.

Now my stored procedure is 257 lines!

Don't worry about the number of lines. As long as the stored procedure extract what you want in the required timing. You should be worrying about the performance of the stored procedure.

FYI. One of my most complicated Stored Procedure is about 1000 lines

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 03:17:29

>>One of my most complicated Stored Procedure is about 1000 lines

No wonder as you enjoy playing with T-SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources