Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

case statement in stored procedure Expand / Collapse
Author
Message
Posted Wednesday, July 09, 2008 11:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 8:32 PM
Points: 134, Visits: 54
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
Post #531097
Posted Wednesday, July 09, 2008 11:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 8:32 PM
Points: 134, Visits: 54
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?
Post #531126
Posted Wednesday, July 09, 2008 11:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 22,507, Visits: 30,225
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #531131
Posted Wednesday, July 09, 2008 12:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 22,507, Visits: 30,225
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #531139
Posted Wednesday, July 09, 2008 1:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #531185
Posted Friday, July 11, 2008 1:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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 here

Post #532186
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse