evanburen
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
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
Try using and IF Stetement since CASE needs to be used within the SELECT statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IF @level = 1
BEGIN
SET @SqlQry = 'your select query'
END
ELSE IF @level = 2
BEGIN
SET @SqlQry = 'your select query'
END
.......
EXEC @SqlQry
BEGIN
SET @SqlQry = 'your select query'
END
ELSE IF @level = 2
BEGIN
SET @SqlQry = 'your select query'
END
.......
EXEC @SqlQry
ASKER
Thank you