How do i use a case statement - 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

General SQL Server Forums

New to SQL Server Programming

How do i use a case statement

Author

Topic

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2012-11-13 : 15:48:55

Hi i am trying use a case statement based on the result of a User defined function, The function returns Tinyint
based on the three results possible i.e;(0, 1, 2)
i need to print a set a message and a indication code
the code is shown below but i am not able to compile it as my syntax is not correct, Please help me!!!!!!!!

Declare @vStatus TinyInt,
@pLogin_UserId int,
@pSession_Id int,
@omessage varchar(250),
@osuccessind int

set @pLogin_UserId = 5
set @pSession_Id = 3976

Execute @vStatus = Udf_Check_UserSession @pLogin_UserId, @pSession_Id

Select @vStatus when 1 then @oMessage = 'test 1', @osuccessind = 0
When 2 then @oMessage = 'test 2', @osuccessind = 0
When 3 then @oMessage = 'test 3', @osuccessind = 0

select @omessage, @osuccessind

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 15:58:07

Syntax like this:

DECLARE @vStatus           TINYINT,
        @pLogin_UserId     INT,
        @pSession_Id       INT,
        @omessage          VARCHAR(250),
        @osuccessind       INT

SET @pLogin_UserId = 5
SET @pSession_Id = 3976

EXECUTE @vStatus = Udf_Check_UserSession @pLogin_UserId, @pSession_Id

SELECT 
	@oMessage = 
		CASE @vStatus
			WHEN 1 THEN 'test 1'
			WHEN 2 THEN 'test 2'
			WHEN 3 THEN 'test 3'
			ELSE 'test ?'
		END,
	@osuccessind =
		CASE 
			WHEN @vStatus IN (1,2,3) THEN 0
			ELSE 1
		END 
SELECT @omessage,
       @osuccessind

If you are not using the @omessage and @osuccessind variables anywhere else, you could bypass those

....
EXECUTE @vStatus = Udf_Check_UserSession @pLogin_UserId, @pSession_Id
     
SELECT
	CASE @vStatus
		WHEN 1 THEN 'test 1'
		WHEN 2 THEN 'test 2'
		WHEN 3 THEN 'test 3'
		ELSE 'test ?'
	END AS OMessage,
	CASE 
		WHEN @vStatus IN (1,2,3) THEN 0
		ELSE 1
	END AS Osuccessind

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2012-11-14 : 11:07:46

Hi Sunitabeck,
Thank you very much that was very helpfull.

quote:

Originally posted by sunitabeck

Syntax like this:

DECLARE @vStatus           TINYINT,
        @pLogin_UserId     INT,
        @pSession_Id       INT,
        @omessage          VARCHAR(250),
        @osuccessind       INT

SET @pLogin_UserId = 5
SET @pSession_Id = 3976

EXECUTE @vStatus = Udf_Check_UserSession @pLogin_UserId, @pSession_Id

SELECT 
	@oMessage = 
		CASE @vStatus
			WHEN 1 THEN 'test 1'
			WHEN 2 THEN 'test 2'
			WHEN 3 THEN 'test 3'
			ELSE 'test ?'
		END,
	@osuccessind =
		CASE 
			WHEN @vStatus IN (1,2,3) THEN 0
			ELSE 1
		END 
SELECT @omessage,
       @osuccessind

If you are not using the @omessage and @osuccessind variables anywhere else, you could bypass those

....
EXECUTE @vStatus = Udf_Check_UserSession @pLogin_UserId, @pSession_Id
     
SELECT
	CASE @vStatus
		WHEN 1 THEN 'test 1'
		WHEN 2 THEN 'test 2'
		WHEN 3 THEN 'test 3'
		ELSE 'test ?'
	END AS OMessage,
	CASE 
		WHEN @vStatus IN (1,2,3) THEN 0
		ELSE 1
	END AS Osuccessind

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources