Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use CASE statement in Procedure Expand / Collapse
Author
Message
Posted Monday, July 13, 2009 8:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 23, 2014 12:34 PM
Points: 24, Visits: 126
Hi All

I have to 3 sql statements to compare the values in the dimension table.

All i need is i want do is i need to create the SQL procedure which return either 0 or 1 by keeping these 3 SQL statements in the CASE one by one where it has to return either 0 or 1 i.e if 1st,2nd or 3rd select statement returns any value it has to return '1' else it has to return '0' then the procedures also has to return either 0 or 1 accordingly.


Pls help me out in this issue..
Thanks In Advance

Post #752090
Posted Monday, July 13, 2009 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:51 AM
Points: 174, Visits: 439
Greetings,

Not sure if you are asking for putting a CASE inside a stored procedure or if you want a single SELECT to use the CASE statement. It sounds like you already have 3 different SELECT statements to return your values and just want to have a flag turned on if any of them return a result. If so, then maybe this will help you.


DECLARE @CheckFlag tinyint

SET @CheckFlag = 0

IF EXISTS(SELECT * FROM MyTable WHERE Checks1 = true)
BEGIN
SET @ChecksFlag = 1
END

IF EXISTS(SELECT * FROM MyTable WHERE Checks2 = true)
BEGIN
SET @ChecksFlag = 1
END

IF EXISTS(SELECT * FROM MyTable WHERE Checks3 = true)
BEGIN
SET @ChecksFlag = 1
END

RETURN(@ChecksFlag) -- OR Just use it from the procedure.


Post #752098
Posted Monday, July 13, 2009 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 23, 2014 12:34 PM
Points: 24, Visits: 126
Hi Thanks For the reply

ya i have 3 sql statements , it may return the value or may not return the value .If the 1st statement returns value then end the procedure by returning flag as "1" else go to 2nd SQL statement and it may return the value or may not return the value and if returns any value then end the procedure and return the flag as "1" and same in the case of 3rd...if all the 3 sql statements dont return the value end the procedure which returns "0" .... hope u got my problem...pls can u provide a proceedure for this?


Thanks in advance
Post #752127
Posted Monday, July 13, 2009 9:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 42,493, Visits: 35,564
Case is only used within a query. You'll have to use IF statements, like what Terrance has shown you.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #752130
Posted Monday, July 13, 2009 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:51 AM
Points: 174, Visits: 439
Greetings,

If your concern is to not run all of the checks, then you can modify the IF statements as such:

IF EXISTS(SELECT * FROM MyTable WHERE Checks2 = true) AND @ChecksFlag = 0
BEGIN
SET @ChecksFlag = 1
END


Another option may be to set @ChecksFlag to a value between 1 and 3 to show which check it was validated against if you needed or wanted to know.

Have a good day.

Terry Steadman
Post #752150
Posted Friday, July 17, 2009 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 28, 2010 3:03 AM
Points: 18, Visits: 106
Hi,

This thing can be acheived using one querry also. I am not sure that all the three conditions need to be checked in the same table or different tables.

-- If it is same table
SELECT COUNT(TOP 1 1) AS Check
FROM MyTable
WHERE Checks1 = true
OR Checks2 = true
OR Checks3 = true

-- If it is in different tables
SELECT COUNT(TOP 1 1) AS Check -- 'Here i have used 1 as a contant value so that it
returns 1 if any of ur conditions are satisfied
FROM MyTable1
WHERE Checks1 = true
OR EXISTS(SELECT 1
FROM MyTable2 WHERE Checks2 = true)
OR EXISTS(SELECT 1
FROM MyTable3 WHERE Checks3 = true)
Post #754783
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse