Cursor with Table Name as Variable in Stored Proc
-
Hi All,
I have a stored procedure which works perfectly when the Table name in the Cursor query is hard coded. I am attempting to pass in a table name to the SP. When I use the variable @TableName in the cursor, SQL asks me to declare the variable which is already declared in the proc.
CREATE PROCEDURE GetCallsDistribution_QtrHr
(
@account VarCHAR(11)='',
@TableName VARCHAR(50)='',
@DateRange bit =1,
@HourofDay VARCHAR(10)='',
@SQLState VARCHAR(8000)='' )
as
DECLARE cur_Cases CURSOR FOR SELECT distinct (HourofDay+':'+QtrHr) as HourofDay FROM @TableName order by HourofDay+':'+QtrHr
OPEN cur_Cases --Open the CURSOR
FETCH NEXT FROM cur_Cases INTO @HourofDay
WHILE @@FETCH_STATUS = 0 --As Long as we got data keep going.
.....etc
I have tried setting a different variable to 'db.dbo.' + @tablename but that generates syntax errors in the cursor statement. Will the Cursor select statement not allow a Column or tablenames to be a variable? I don't have any problem with local variables in where, group or order statements.
Any help appreciated!!
Steve
-
A Cursor Declaration requires a valid select statement
SELECT distinct (HourofDay+':'+QtrHr) as HourofDay FROM @TableName order by HourofDay+':'+QtrHr
is not a valid select statement.
Try evaluating the table name before you declare the cursor then use the select statement for that table.
If @TableName = 'Mytable'
DECLARE cur_Cases CURSOR FOR SELECT distinct (HourofDay+':'+QtrHr) as HourofDay FROM mMytable order by HourofDay+':'+QtrHr
else if @TableName = 'HisTable'
DECLARE cur_Cases CURSOR FOR SELECT distinct (HourofDay+':'+QtrHr) as HourofDay FROM HisTable order by HourofDay+':'+QtrHr
Not sure if this will work but pointing out that the select cannot be dynamic.
Perhaps you should post your the reason you are using a cursor.
I find very few reasons to use cursors, and It does not appear that you are properly using a cursor.
-
This is one of two stored procedures I am trying to develop to display a call distribution. I was hoping to do it this way to stay awau from more complex statements. I first query from CallHistory to get the call counts by Account, hour,QTRHour etc. I tried to build a named temp table, i.e. #Distribution, but can't see that from the new stored Proc either. With this proc, and with the cursor specifically, I am building a pivot table to then display the calls horizontally by account, by date range. This displays on a chart so the managers can staff the call centers properly.
-
Certainly you should be able to do that without using a cursor.
Can you post a test table with some sample data, and show an example how you want the results to look.
For Example, I created a call history table with some records,
Create table CallHistory (pk int identity, AccountID char(1), CallTime smalldatetime)
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:07')
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:10')
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:37')
Insert into CallHistory (AccountID, CallTime)
Values('B', '06/24/2005 12:47')
Insert into CallHistory (AccountID, CallTime)
Values('B', '06/24/2005 11:30')
Insert into CallHistory (AccountID, CallTime)
Values('C', '06/24/2005 12:07')
Insert into CallHistory (AccountID, CallTime)
Values('D', '06/24/2005 12:55')
Insert into CallHistory (AccountID, CallTime)
Values('E', '06/24/2005 11:07')
Select datepart(mm,CallTime) from CallHistory
Select count(*) as #Calls, AccountID, [Hour], QuarterHour
from (select AccountID, datepart(hh,CallTime) as [Hour],
Case when datepart(mi,CallTime) between 0 and 14
then 'Q1'
when datepart(mi,CallTime) between 15 and 29
then 'Q2'
when datepart(mi,CallTime) between 30 and 44
then 'Q3'
when datepart(mi,CallTime) between 45 and 59
then 'Q4'
End as QuarterHour
from CallHistory) as DerivedTable
Group by AccountID, [Hour], QuarterHour
Drop table CallHistory
Came up with this
#Calls AccountID Hour QuarterHour
----------- --------- ----------- -----------
2 A 2 Q1
1 A 2 Q3
1 B 11 Q3
1 B 12 Q4
1 C 12 Q1
1 D 12 Q4
1 E 11 Q1
-
Exactly. The SP I presented the beginning of then takes the data in your example and pivots it using a cursor( I only know how to do a pivot table using a cursor) and the results look like:
AccountID 2-Q1 2-Q2 2-Q3 2-Q4 3-Q1 3-Q2 3-Q3 ..................
VA1120 2 0 4 6 15 12 1
13431 4 5 1 7 1 8 3
and so on. I can then analyse the call distribution for each client to decide how many calls / minutes I need for each hour of the day and the splits by quarter hour. Using one account we can help them, using all accounts I help us.
What I was trying to do was allow multiple users to do the queries without conflict. I wanted to put them in a derived table and send the table name into the stored procedure to produce the results. If I have a named table in the query, one might be dropping the table another has just filled before the query against it takes place. By using a table name (temp+UserID) I have unique tables for each user and they won't step on each other. Hence, I was trying to pass in a table name of a real table, saved in the database but it loses the reference I guess when you assign it to a local variable.
-
Yay, you finally posted what you wanted your output to look like.
You definatelly do not need a cursor to pivot data.
Pivot data TUT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp
Create table CallHistory (pk int identity, AccountID char(1), CallTime smalldatetime)
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:07 AM')
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:10 AM')
Insert into CallHistory (AccountID, CallTime)
Values('A', '06/24/2005 2:37')
Insert into CallHistory (AccountID, CallTime)
Values('B', '06/24/2005 12:47 AM')
Insert into CallHistory (AccountID, CallTime)
Values('B', '06/24/2005 1:30 AM')
Insert into CallHistory (AccountID, CallTime)
Values('C', '06/24/2005 12:07 AM')
Insert into CallHistory (AccountID, CallTime)
Values('D', '06/24/2005 12:55 AM')
Insert into CallHistory (AccountID, CallTime)
Values('E', '06/24/2005 1:07 AM')
Select AccountID,
Sum(case when [Hour] = 0 and QuarterHour = 'Q1'
then 1
else 0
end) as [0-1],
sum(case when [Hour] = 0 and QuarterHour = 'Q2'
then 1
else 0
end) as [0-2],
Sum(case when [Hour] = 0 and QuarterHour = 'Q3'
then 1
else 0
end) as [0-3],
Sum(case when [Hour] = 0 and QuarterHour = 'Q4'
then 1
else 0
end) as [0-4],
Sum(case when [Hour] = 1 and QuarterHour = 'Q1'
then 1
else 0
end) as [1-1],
Sum(case when [Hour] = 1 and QuarterHour = 'Q2'
then 1
else 0
end) as [1-2],
Sum(case when [Hour] = 1 and QuarterHour = 'Q3'
then 1
else 0
end) as [1-3],
Sum(case when [Hour] = 1 and QuarterHour = 'Q4'
then 1
else 0
end) as [1-4],
Sum(case when [Hour] = 2 and QuarterHour = 'Q1'
then 1
else 0
end) as [2-1],
Sum(case when [Hour] = 2 and QuarterHour = 'Q2'
then 1
else 0
end) as [2-2],
Sum(case when [Hour] = 2 and QuarterHour = 'Q3'
then 1
else 0
end) as [2-3],
Sum(case when [Hour] = 2 and QuarterHour = 'Q4'
then 1
else 0
end) as [2-4]
-- ETC Keep adding Hours up to 24
from (select AccountID, datepart(hh,CallTime) as [Hour],
Case when datepart(mi,CallTime) between 0 and 14
then 'Q1'
when datepart(mi,CallTime) between 15 and 29
then 'Q2'
when datepart(mi,CallTime) between 30 and 44
then 'Q3'
when datepart(mi,CallTime) between 45 and 59
then 'Q4'
End as QuarterHour
from CallHistory) as DerivedTable
Group by AccountID
Drop table CallHistory
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply