Hi,
I need help with conventions on turning a script into a stored procedure. here's the script:
/* Version 1.0
DESCRIPTION
Create Temporary Tables for Updating Minimum and Maximum Rent on Floorplan table
Created by d.meehan 4/23/2010 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEMP_UPDATE_FLOORPLAN]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEMP_UPDATE_FLOORPLAN];
CREATE TABLE TEMP_UPDATE_FLOORPLAN (
hproperty numeric(18, 0) NOT NULL ,
hfloorplan numeric(18, 0) NOT NULL ,
sname varchar(50) NULL,
dminrent numeric (21,6) NULL,
dmaxrent numeric (21,6) NULL,
datecreated datetime NULL);
/* Insert records into TEMP_UPDATE_FLOORPLAN */
INSERT INTO temp_update_floorplan ( hproperty, hfloorplan, sname, dminrent, dmaxrent, datecreated)
(select
p.hmy,
f.hmy,
f.sname,
min(u.srent),
max(u.srent),
Getdate()
from unit u
inner join unittype ut on ut.hmy = hunittype
inner join property p on p.hmy = u.hproperty
inner join floorplanxref fx on fx.hunittype = ut.hmy
inner join floorplan f on f.hmy = fx.hfloorplan
where p.binactive = 0
and substring(ut.scode,1,1) not in ('x', 'd')
and substring(ut.scode,5,1) not in ('x', 'z')
and substring(u.scode,1,4) <> 'WAIT'
and isnull(u.bportalexcluded,0) = 0
and ut.sdesc not like '%AFFORD%'
and u.srent <> 0
group by p.hmy,
f.scode, f.sname, f.hmy);
/* Update Floorplan with TEMP_UPDATE_FLOORPLAN min/max rents. */
update floorplan set
drentlow = tf.dminrent,
drenthigh = tf.dmaxrent
from temp_update_floorplan tf
where tf.hfloorplan = floorplan.hmy;
Any help would be appreciated - thanks!
I need help with conventions on turning a script into a stored procedure. here's the script:
/* Version 1.0
DESCRIPTION
Create Temporary Tables for Updating Minimum and Maximum Rent on Floorplan table
Created by d.meehan 4/23/2010 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEMP_UPDATE_FLOORPLAN]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEMP_UPDATE_FLOORPLAN];
CREATE TABLE TEMP_UPDATE_FLOORPLAN (
hproperty numeric(18, 0) NOT NULL ,
hfloorplan numeric(18, 0) NOT NULL ,
sname varchar(50) NULL,
dminrent numeric (21,6) NULL,
dmaxrent numeric (21,6) NULL,
datecreated datetime NULL);
/* Insert records into TEMP_UPDATE_FLOORPLAN */
INSERT INTO temp_update_floorplan ( hproperty, hfloorplan, sname, dminrent, dmaxrent, datecreated)
(select
p.hmy,
f.hmy,
f.sname,
min(u.srent),
max(u.srent),
Getdate()
from unit u
inner join unittype ut on ut.hmy = hunittype
inner join property p on p.hmy = u.hproperty
inner join floorplanxref fx on fx.hunittype = ut.hmy
inner join floorplan f on f.hmy = fx.hfloorplan
where p.binactive = 0
and substring(ut.scode,1,1) not in ('x', 'd')
and substring(ut.scode,5,1) not in ('x', 'z')
and substring(u.scode,1,4) <> 'WAIT'
and isnull(u.bportalexcluded,0) = 0
and ut.sdesc not like '%AFFORD%'
and u.srent <> 0
group by p.hmy,
f.scode, f.sname, f.hmy);
/* Update Floorplan with TEMP_UPDATE_FLOORPLAN min/max rents. */
update floorplan set
drentlow = tf.dminrent,
drenthigh = tf.dmaxrent
from temp_update_floorplan tf
where tf.hfloorplan = floorplan.hmy;
Any help would be appreciated - thanks!
then add CREATE PROCEDURE myProcedure AS to your code above.
