case statement in stored procedure

  • Hi all,

    I have a stored procedure. But it seems that you cannot use variable for a table. variables are only can be used in condition expression? What about case statement.

    I keep getting the following message:

    Msg 156, Level 15, State 1, Procedure ExtractDataforProducer, Line 5

    Incorrect syntax near the keyword 'Case'.

    Msg 1087, Level 15, State 2, Procedure ExtractDataforProducer, Line 7

    Must declare the table variable "@TableName".

    Msg 1087, Level 15, State 2, Procedure ExtractDataforProducer, Line 13

    Must declare the table variable "@TableName".

    //////////////////////////////////

    Create proc ExtractDataforProducer (@StartDate Datetime, @EndDate Datetime, @SubProducerFlag char(1), @TableName varchar(15), @AgentCode varchar(10))

    As

    Case @SubProducerFlag

    When 'Y' Then

    Select * from @TableName tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode like @AgentCode

    and convert(char(10), vs.txndatetime,126) between convert(char(10),@StartDate,126) and convert(char(10), @EndDate, 126)

    order by vs.txndatetime

    Else

    Select * from @TableName tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode=@AgentCode

    and convert(char(10), vs.txndatetime,126) between convert(char(10),@StartDate,126) and convert(char(10), @EndDate, 126)

    order by vs.txndatetime

    End

    Go

  • Hi all,

    I rewrite to another one.

    Create proc ExtractDataforProducer (@StartDate Datetime, @EndDate Datetime, @SubProducerFlag char(1), @TableName varchar(15), @AgentCode varchar(10))

    As

    Select * from @TableName tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode

    Case When @SubProducerFlag='Y'

    Then like End,

    Case When @SubProducerFlag !='Y'

    Then = End

    @AgentCode

    and convert(char(10), vs.txndatetime,126) between convert(char(10),@StartDate,126) and convert(char(10), @EndDate, 126)

    order by vs.txndatetime

    But I still get an error message:

    Msg 1087, Level 15, State 2, Procedure ExtractDataforProducer, Line 3

    Must declare the table variable "@TableName".

    Maybe the table cannot be variable here?

  • Betty Sun (7/9/2008)


    Hi all,

    I have a stored procedure. But it seems that you cannot use variable for a table. variables are only can be used in condition expression? What about case statement.

    I keep getting the following message:

    Msg 156, Level 15, State 1, Procedure ExtractDataforProducer, Line 5

    Incorrect syntax near the keyword 'Case'.

    Msg 1087, Level 15, State 2, Procedure ExtractDataforProducer, Line 7

    Must declare the table variable "@TableName".

    Msg 1087, Level 15, State 2, Procedure ExtractDataforProducer, Line 13

    Must declare the table variable "@TableName".

    //////////////////////////////////

    Create proc ExtractDataforProducer (@StartDate Datetime, @EndDate Datetime, @SubProducerFlag char(1), @TableName varchar(15), @AgentCode varchar(10))

    As

    Case @SubProducerFlag

    When 'Y' Then

    Select * from @TableName tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode like @AgentCode

    and convert(char(10), vs.txndatetime,126) between convert(char(10),@StartDate,126) and convert(char(10), @EndDate, 126)

    order by vs.txndatetime

    Else

    Select * from @TableName tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode=@AgentCode

    and convert(char(10), vs.txndatetime,126) between convert(char(10),@StartDate,126) and convert(char(10), @EndDate, 126)

    order by vs.txndatetime

    End

    Go

    CASE is a function, not a control flow statement. In this case, you need to use the IF ELSE construct.

    😎

  • Also, you can't use a variable in a select statement for a tablename, unless the variable itself is a table variable (confused?).

    What you are trying to do would require the use a dynamic sql. Read BOL for more info, then ask more questions for clarity.

    😎

  • I have a question: Based on your queries, it looks like you have multiple tables with the same columns. Is that correct?

    The way you're trying to use Case looks like Visual Basic. It doesn't work that way in SQL.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Dynamic SQL is the solution.

    Generate the query in your sp and Execute it with EXEC.

    I tried by the following code;

    Declare @Sql varchar(max)

    Set @Sql = 'Select * from ' + @TableName + ' tn

    inner join tis_vs vs on tn.pnref=vs.pnref

    and agentCode '

    if @SubProducerFlag='Y' Set @Sql = @Sql + ' like ''' + @AgentCode + '%'''

    if @SubProducerFlag !='Y' Set @Sql = @Sql + ' = ''' + @AgentCode + ''''

    Set @Sql = @Sql + ' and convert(char(10), vs.txndatetime,126) between ''' + convert(char(10), @StartDate ,126) + '''

    and ''' + convert(char(10), @EndDate , 126) + ''' order by vs.txndatetime '

    Print @Sql

    Exec @Sql

    I hope it will help you. Remember that there are alot of issues regarding string processing like SQL injection.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply