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

CASE

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 10:55:11

Hello,

I am verifying various inputs of an SQL procedure.
When an input is invalid according to a certain condition I want to give it a numeric value to an OUTPUT parameter, return it and stop from running the remaining procedure code.

I was using IF but now I started using CASE:

SELECT CASE @FEEDBACK
WHEN NOT EXISTS (SELECT * FROM dbo.Documents WHERE DocumentId = @DocumentId) THEN -1
WHEN NOT EXISTS (SELECT * FROM dbo.Users WHERE UserId = @UserId) THEN -2
END

This is not working.
And do I need to place a RETURN @Feedback inside each WHEN?

And finally, is this the way to do this?

Thanks,
Miguel

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-12 : 10:56:18

no.

you have to use if.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:01:41

What to do then BOTH cases are true?

CREATE PROCEDURE dbo.uspGetMyResultBack
(
	@DocumentID INT,
	@UserID INT,
	@Feedback BIT OUT
)
AS

SET NOCOUNT ON

DECLARE	@Doc BIT,
	@Usr BIT

IF EXISTS (SELECT * FROM dbo.Documents WHERE DocumentId = @DocumentId)
	SELECT	@Doc = 0
ELSE
	SELECT	@Doc = -1

IF EXISTS (SELECT * FROM dbo.Users WHERE UserId = @UserId)
	SELECT	@Usr = 0
ELSE
	SELECT	@Usr = -2

SELECT	@Doc + @Usr

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources