CASE Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

CASE Question

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-01 : 11:52:01

I have a stored procedure that works like this: (the following is a mock up)

CREATE PROCEDURE spGetInfo
(
@spType smallint
)
AS
SET NOCOUNT ON

IF (@spType=1)
BEGIN
SELECT Count(*)
AS "counter"
FROM Cart_Inventory
WITH (NOLOCK)
END

IF (@spType=2)
BEGIN
SELECT Count(*)
AS "counter"
FROM Cart_Orders
WITH (NOLOCK)
WHERE OrderStatusId > 1
END

What I really want to do this is.

CREATE PROCEDURE spGetInfo
(
@spType smallint
)
AS
SET NOCOUNT ON

CASE @spType
WHEN 1 THEN
BEGIN
SELECT Count(*)
AS "counter"
FROM Cart_Inventory
WITH (NOLOCK)
END

WHEN 2 THEN
BEGIN
SELECT Count(*)
AS "counter"
FROM Cart_Orders
WITH (NOLOCK)
WHERE OrderStatusId > 1
END

But this won't compile. I have tried several combinations. Is there a way to determine which query to run within a stored procedure using CASE?

Thanks

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-01 : 12:08:39

Hi there,

This is a really quick answer but I hope it will help you.

Have you tried the following approach?


DECLARE @ME INT

SET @ME = 2

IF	@ME = 2  GOTO FIRSTSECTION ELSE GOTO EXITPOINT 

FIRSTSECTION:
PRINT 'HELO'
GOTO EXITPOINT

SECONDSECTION:
PRINT 'BYE!'
GOTO EXITPOINT

EXITPOINT:
PRINT 'DONE!'

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-01 : 12:11:43

The short answer is no. There is no T-SQL equivalent to the "switch" or "select case" constructs availabe in application languages.

Most of the time, this question gets raised because an application developer is writing database objects and is using application-style design principles.

Jonathan
{0}

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-01 : 12:16:53

Hello, yes, that would work. But what if I have several IF statements to go through? With the GOTO, I would still have to process several IF statements.

What I am trying to do is to avoid using too many IF statments and use CASE instead.

Basically, this stored procedure returns counts for many things. I know that I could just split them up into separate procs, but since they are so common and simple, I wanted to keep them all in one stored proc and use the CASE statement to determine which query to run.

Thanks for your help!

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-01 : 12:17:51

Ok. Thanks for all for your replies!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-01 : 12:29:16

if @spType=1
begin

end
else if @spType=2
begin

end
else if @spType=3
begin

end
else if @spType=4
begin

end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-01 : 12:53:25

I don't know why you don't like you're original syntax but...it's alot more overhead to do what you're asking..maybe I shouldn't....oh what the hell...




CREATE TABLE myTable99 (col1 char(1))
GO

INSERT INTO myTable99
SELECT 'Q' UNION ALL
SELECT 'W' UNION ALL
SELECT 'E' UNION ALL
SELECT 'R' UNION ALL
SELECT 'T' UNION ALL
SELECT 'Y'
GO

CREATE TABLE myTable00 (col1 char(1))
GO

INSERT INTO myTable00
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

DECLARE @x int

SELECT @x = 1

SELECT CASE WHEN @x = 1 THEN Count_00
	    WHEN @x = 2 THEN Count_99
			ELSE 0
       END
  FROM (	
	SELECT    (SELECT COUNT(*) FROM myTable00) AS Count_00
		, (SELECT COUNT(*) FROM myTable99) AS Count_99
	) AS XXX
GO

DROP TABLE myTable00
GO

DROP TABLE myTable99
GO

I feel dirty

Brett

8-)

Edited by - x002548 on 07/01/2003 12:54:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources