help with syntax (select case)

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)

help with syntax (select case)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-04 : 04:36:11

Hi,

I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..

I have my SPROC configured as below, can anyone help me out as to why its not working?

btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)


IF @genderID > 2
.. 
ELSE
........WHERE genderID = @genderID

thanks very much once again!
mike123



CREATE PROCEDURE [dbo].[select_123]
(
	@genderID tinyint
)
	 
AS SET NOCOUNT ON


SELECT 

	CASE @genderID
		WHEN 1 THEN
 
			SELECT  TOP 40  * 

			FROM     tbl   
			WHERE .......


	    WHEN 2  THEN
 
			SELECT  TOP 40  * 

			FROM     tbl   
			WHERE .......
	ELSE
		SELECT  TOP 40  * 

			FROM     tbl   
			WHERE .......



	END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 04:40:28

CASE can only return a scalar value, not resultsets.

IF @Gender = 1
	SELECT	*
	FROM	Tbl
	WHERE	...

IF @Gender = 2
	SELECT	*
	FROM	Tbl
	WHERE	...

IF @Gender = 3
	SELECT	*
	FROM	Tbl
	WHERE	...


SELECT	*
FROM	Table
WHERE	@Gender = 1
		AND	(
				...
			)
	OR @Gender = 2
		AND	(
				...
			)
	OR @Gender = 3
		AND	(
				...
			)

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 04:41:38

[code]Select * from Tbl where @genderID = 1 and ...
union all
Select * from Tbl where @genderID = 2 and ...
union all
Select * from Tbl where @genderID = 3 and ...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-04 : 06:20:24

perfect, thanks once again :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources