July 9, 2008 at 11:14 am
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
July 9, 2008 at 11:46 am
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?
July 9, 2008 at 11:50 am
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.
😎
July 9, 2008 at 12:04 pm
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.
😎
July 9, 2008 at 1:23 pm
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
July 11, 2008 at 1:25 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply