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