SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do i use a case statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 11/13/2012 :  15:48:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  15:58:07  Show Profile  Reply with Quote
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
Go to Top of Page

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 11/14/2012 :  11:07:46  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000