Link to home
Start Free TrialLog in
Avatar of evanburen
evanburenFlag for United States of America

asked on

Help with SQL Case statement

This was working fine until I needed to add an additional CASE @Level statement. I need to run a different select statement based on the level number passed to it. Thanks

CREATE PROCEDURE p_CashSample2
@Level Int = NULL,
@RecordCount Int = NULL OUTPUT
AS
SELECT @RecordCount = COUNT(*)
FROM PopulationBondsCash_Temp

CASE @Level
WHEN 1 THEN
-- *** Level 1 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @RecordCount BETWEEN 9 AND 15 THEN 2
ELSE 125
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

WHEN 2 THEN

-- *** Level 2 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @RecordCount BETWEEN 9 AND 15 THEN 3
ELSE 1250
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

WHEN 3 THEN
-- *** Level 3 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 3
WHEN @RecordCount BETWEEN 9 AND 15 THEN 5
ELSE 2000
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

END
Avatar of TonyReba
TonyReba
Flag of United States of America image

Try using and IF Stetement since CASE needs to be used within the SELECT statement.
ASKER CERTIFIED SOLUTION
Avatar of Chandan_Gowda
Chandan_Gowda
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
IF @level = 1
   BEGIN
         SET @SqlQry = 'your select query'
   END
ELSE IF @level = 2
   BEGIN
        SET @SqlQry = 'your select query'
   END
.......

EXEC @SqlQry
Avatar of evanburen

ASKER

Thank you