• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
evanburen
Asked:
evanburen
1 Solution
 
TonyRebaCommented:
Try using and IF Stetement since CASE needs to be used within the SELECT statement.
0
 
Chandan_GowdaCommented:
CREATE PROCEDURE p_CashSample2
@Level Int = NULL,
@RecordCount Int = NULL OUTPUT
AS
SELECT @RecordCount = COUNT(*)
FROM PopulationBondsCash_Temp

IF @Level =1
BEGIN
-- *** 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()
END
IF @Level=2
BEGIN
      -- *** 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()
END
IF @Level=3
-- *** Level 3 ***
BEGIN
      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
0
 
rajvjaCommented:
IF @level = 1
   BEGIN
         SET @SqlQry = 'your select query'
   END
ELSE IF @level = 2
   BEGIN
        SET @SqlQry = 'your select query'
   END
.......

EXEC @SqlQry
0
 
evanburenAuthor Commented:
Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now