September 4, 2007 at 11:26 pm
Hi,
I am trying to automate the procedure wherein I want to achieve the below given code for all the sitecode column in table. I want to loop for all site code and need to pass year,month having (db1_Electricity_Usage-Gmers_Electricity_Usage)<>0 to another stored procedure which accepts year and month column
DECLARE sitecode_cursor CURSOR FOR
SELECT distinct sitecode from db1..site where len(sitecode)=8
ORDER BY userid
OPEN sitecode_cursor
FETCH NEXT FROM sitecode_cursor
INTO @sitecode
WHILE @@FETCH_STATUS = 0
insert into table1
SELECT table2.year,table2.month,left(table3.table3code,8),SUM(table2.electricityuse) 'ElecUse' FROM db1..table2 table2,db1..table3 table3 WHERE table3id
IN(SELECT idtable3 FROM db1..table3 WHERE table3code = @sitecode)
AND (year*100+month) BETWEEN 200511 AND 200706 AND idsvc=100 GROUP BY month, year,table3.table3code--, electricityuse
ORDER BY year, month
insert into table4
select year(rpt_dt) YEAR ,month(rpt_dt) MONTh, left (table3code,8) BU, SUM(KWh_Purchased_Quantity)'ElecUse'
from db2..table5
where table3code in
(select table3code from db1..table3 where table3code LIKE @sitecode) and len(table3code ) > 8 --)
and rpt_dt >= '2005-11-01' and rpt_dt < '2007-07-01' group by rpt_dt,left (table3code,8)
order by RPT_DT
select *,(db1_Electricity_Usage-Gmers_Electricity_Usage)as Diff from table1,table4
where table1.table3code=table4.gmmerstable3code and (db1_Electricity_Usage-Gmers_Electricity_Usage) <>0
and table1.year=table4.year and table1.month=table4.month.
Plz suggest how to achieve this or help me in writing this code soon.
Thanks
September 5, 2007 at 6:01 am
As much as I hate to suggested it, the only way you can do what you want is through the use of dynamic SQL. Stored procedures and Cursors don't like using Variables as part of their statements.
Here's a snippet of an example of building a dynamic SQL Statement. You'll have to play with it though. I've never built a cursor dynamically, just statements within a cursor.
Declare @MyCursor varchar(8000)
Set @MyCursor = 'DECLARE sitecode_cursor CURSOR FOR
SELECT distinct sitecode from db1..site where len(sitecode)=8
ORDER BY userid
OPEN sitecode_cursor
FETCH NEXT FROM sitecode_cursor
INTO ' + @sitecode + ' WHILE @@FETCH_STATUS = 0'
Exec @MyCursor
The caveat to the above is that you might have to add each individual line separately (with its own single quotes and a plus sign between each line of code to concateonate it) because of the whole carriage-return issue.
Hope this helps.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply