CODEWITHSUNDEEP
sqlsql-server-2008
Julien698
Julien698

Reputation: 716

CASE WHEN THEN with subquery

I try to do a switch like c# in sql or if with differents conditions.

I use that but it don't work at all :

it's for a stored procedure :

CREATE PROCEDURE [dbo].[Mystoredprocedure]
@Value INT
AS 
BEGIN

       SET NOCOUNT ON;
CASE

WHEN @Value = 0
THEN SELECT * FROM mytable1


WHEN @Value = 1
THEN SELECT * FROM mytable2

WHEN @Value = 2
THEN SELECT * FROM mytable3

END

But it doesn't work, i search examples but i don't find what i want really.

I try too :

    CREATE PROCEDURE [dbo].[Mystoredprocedure]
    @Value INT
    AS
    BEGIN
          SET NOCOUNT ON;

    IF @Value = 0
    BEGIN
         SELECT * FROM MyTable1
    END

    ELSE IF @Value = 1
    BEGIN
         SELECT * FROM MyTable2
    END

    ELSE IF @Value = 2
    BEGIN 
         SELECT * FROM Mytable3
    END

END

Have you the solution to fix that ? Thanks !

Upvotes: 0

Views: 66

Answers (2)

Julien698
Julien698

Reputation: 716

Finally i found the solution, it's easy. I've just to remove the else before the if and it works !

CREATE PROCEDURE [dbo].[MystoredProcedure]
(@Value INT)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;



IF @Value = 0
BEGIN   
     SELECT * FROM mytable1
END

IF @Value = 1
BEGIN
     SELECT * FROM mytable2
END

IF @Value = 2
BEGIN
    SELECT * FROM mytable3  
END

IF @Value = 3
BEGIN
    SELECT * FROM mytable4
END

END

Upvotes: 0

Alex Szabo
Alex Szabo

Reputation: 3276

I suggest you try this:

CREATE PROCEDURE [dbo].[Mystoredprocedure] (@Value INT)
AS 
BEGIN

    IF @Value = 0
        BEGIN
            SELECT * FROM mytable1
        END
    ELSE IF @Value = 1
        BEGIN
            SELECT * FROM mytable2
        END
    ELSE IF @Value = 2
        BEGIN
            SELECT * FROM mytable3
        END

END

If I get this right, the problem is that you have a variable in your query, but you don't pass a value to it. You declare it, and it's empty. With this way, if you call the query

EXEC [dbo].[Mystoredprocedure] 1

It should select from mytable 2, etc.

Upvotes: 1

Related Questions