how to pass coulmn values to stored procedure

Question

how to pass coulmn values to stored procedure

satish-348625

SSC Enthusiast

Points: 194
More actions
August 30, 2007 at 1:23 am

#174488

Hi,
I need to automate the procedure of selecting column with numeric and passing those column values as string to another stored procedure.
My aim is to pass sitecode having difference <>0 and from date and todate as an parameter to another stored procedure which updates sitewise for the utility so that in case a particular sitecode with same date range is included in one utility it should not be repeated in another utility.
Here is my sample store procedure
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gmmers1')
DROP TABLE gmmers1
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gmmers2')
DROP TABLE gmmers2
create table gmmers1(year smallint,
month tinyint,
sitecode varchar(30),
Gen_Electricity_Usage bigint
)
create table gmmers2 (year smallint,
month tinyint,
gmmerssitecode varchar(30),
Gmers_Electricity_Usage bigint
)
DECLARE sitecode_cursor CURSOR FOR
SELECT distinct userid from gen..site where len(userid)=8
ORDER BY userid
OPEN sitecode_cursor
FETCH NEXT FROM sitecode_cursor
INTO @sitecode
WHILE @@FETCH_STATUS = 0
BEGIN
insert into gmmers1
SELECT acctrptdata.year,acctrptdata.month,left(site.sitecode,8),SUM(acctrptdata.electricityuse) 'ElecUse' FROM GEN..acctrptdata acctrptdata,gen..site site WHERE siteid
IN(SELECT idsite FROM gen..site WHERE sitecode = 'N0019856')
AND (year*100+month) BETWEEN 200511 AND 200706 AND idsvc=100 GROUP BY month, year,site.sitecode--, electricityuse
ORDER BY year, month
insert into gmmers2
select year(rpt_dt) YEAR ,month(rpt_dt) MONTh, left (sitecode,8) BU, SUM(KWh_Purchased_Quantity)'ElecUse'
from GMERS_PRODN..electricity_data
where sitecode in -- (select sitecode from facility_data where left(sitecode,8) in
(select sitecode from gen..site where sitecode LIKE 'N0019856%') and len(sitecode ) > 8 --)
and rpt_dt >= '2005-11-01' and rpt_dt < '2007-07-01' group by rpt_dt,left (sitecode,8)
order by RPT_DT
select *,(Gen_Electricity_Usage-Gmers_Electricity_Usage)as Diff from gmmers1,gmmers2
where gmmers1.sitecode=gmmers2.gmmerssitecode and (Gen_Electricity_Usage-Gmers_Electricity_Usage) <>0
and gmmers1.year=gmmers2.year and gmmers1.month=gmmers2.month.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

I'm just going to hazard a guess and say that no one has been able to figure out your requirements... Any chance of you rewording them a bit?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

satish-348625 SSC Enthusiast Points: 194 More actions · Answer 2

Resposted my reqirements once again.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=396336