Problem with CASE ...WHEN - 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 2005 Forums

Transact-SQL (2005)

Problem with CASE ...WHEN

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 07:49:14

Could you tell me where the problem of following code is?

Case @DMLType
			WHEN 'I' THEN
				BEGIN --Insert the new row
					INSERT INTO SQL2005_Testdb1_Person
					-- SELECT stmt using OPENXML rowset provider
						SELECT *
						FROM   OPENXML (@idoc, '/t',2)
								 WITH (ID  int,
									   FirstName  varchar(50) ,
									   LastName  varchar(50) ,
									   DateOfBirth  datetime )
				END
		WHEN 'U' THEN
			BEGIN --Update the modified row
				--UPDATE SQL2005_Testdb1_Person SET (
			END 
		ELSE
			DELETE FROM 	SQL2005_Testdb1_Person WHERE ID = @PK
	END  --end case

"@DMLType char(1)" is an input parameter to a stored procedure

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 07:53:22

CASE doesn't work that way in TSQL. It's for run time data substitutions rather than a control of flow structure.

You want a IF ELSE structure instead.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 07:54:57

[code]
IF @DMLType = 'I' BEGIN
INSERT INTO SQL2005_Testdb1_Person
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/t',2)
WITH (ID int,
FirstName varchar(50) ,
LastName varchar(50) ,
DateOfBirth datetime )
END
ELSE IF @DMLType = 'U' BEGIN
--Update the modified row
--UPDATE SQL2005_Testdb1_Person SET (
END
ELSE BEGIN
DELETE FROM SQL2005_Testdb1_Person WHERE ID = @PK
END --end case
[/code]

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 07:57:26

case is used like this.


DECLARE @foo VARCHAR(50)
SET @foo = 'hoo'

SELECT
    CASE @foo
         WHEN 'hoo' THEN 'foo is hoo'
         ELSE 'foo is not hoo'
    END

-------------
Charlie

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 08:08:19

Thanks Charlie
Now I've changed it into IF ELSE but am given error again

IF @DMLType = 'I' 
				BEGIN --Insert the new row
					INSERT INTO SQL2005_Testdb1_Person
					-- SELECT stmt using OPENXML rowset provider
						SELECT *
						FROM   OPENXML (@idoc, '/t',2)
								 WITH (ID  int,
									   FirstName  varchar(50) ,
									   LastName  varchar(50) ,
									   DateOfBirth  datetime )
				END
			ELSE IF  @DMLType = 'U' 
				BEGIN --Update the modified row
					--UPDATE SQL2005_Testdb1_Person SET (
				END 
			ELSE 
				BEGIN
					DELETE FROM 	SQL2005_Testdb1_Person WHERE ID = @PK
				END

"Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near 'END'."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-25 : 08:12:20

You need to execute at least a statement for the second else

Madhivanan

Failing to plan is Planning to fail

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 08:22:12

ahh you're right
sorry
and thanks too much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources