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

....
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

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

---