[dba-SQLServer]Stored Procedures

Jim,

Here's a paired down sproc that shows creating a temp table, using the
data from the temp table and then dropping it.  I've made this so simple
that the temp table isn't really neccesary, but it will give you the
idea.  Let me know if you have questions.  Temp tables work just like
real tables, but the have a # in front of the name.

--Susan


create proc dbo.p_rpt_Activity_Summary
	@IP_Activity_ID int
as
set nocount on
/*
A.  Put all the ip_empjob start rows into a temp table
*/

	select ip_empjob_id, emplid, empl_rcdno
		, effdt as startdate, cast(null as datetime) as enddate 
	into #ip_emp_job_start_end
	from ip_emp_job
	where action not in ('LOA', 'PLA', 'RET', 'SUS', 'SWB', 'TER',
'TWB')

	--select * from #ip_emp_job_start_end
	
/*
B.  Do something with the data in the temp table.
*/

select a.empl_type_desc
	, a.COMPRATE
	, a.AREA
	, a.ORG
	, a.SUBORG
FROM  #ip_emp_job_start_end a inner join tbl_student b
		on a.emplid = b.emplid
where b.ip_activity_id = @ip_activity_id

/*
C.  Drop temp table -- cleaner to drop this explicity, 
even though it will drop when the sproc finishes.
*/

drop table #ip_emp_job_start_end


GO

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] 
Sent: Wednesday, March 05, 2003 10:03 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedures


Hi Susan:

Yes, please.

I could probably figure it out if given enough time but you are skilled
in this. I would greatly appreciate any description or example you could
provide.  :-)

TIA
Jim

-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Susan
Zeller
Sent: Wednesday, March 05, 2003 6:14 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedures


I'm assuming SQL7 has temp tables given Francisco's comment.  If that's
the case, you can do everything in one sproc with temp tables.  I
actually am doing this in several cases.  I create sometimes two or
three temp tables in my sproc, then use those to do something or return
something and then drop the temp table, all in the same sproc.  Let me
know if you need info on how to do this.

--Susan


-----Original Message-----
From: Francisco Tapia [mailto:my.lists at verizon.net]
Sent: Wednesday, March 05, 2003 1:52 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Stored Procedures


There is no way that I know of to do what you are asking in SQL7, viable
workarounds include executing your sproc1, to execute a nested sproc
that writes to a temp table and then refrencing the temp table from
sproc1 long after sproc2 (nested) has executed.

just a wag tho.


-Francisco
http://rcm.netfirms.com/
----- Original Message -----
From: "Jim Lawrence (AccessD)" <accessd at shaw.ca>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, March 04, 2003 11:21 PM
Subject: RE: [dba-SQLServer]Stored Procedures


: Thanks Susan:
:
: Unfortunately, the client is running on SQL7. :-(
:
: Jim
:
: -----Original Message-----
: From: dba-sqlserver-admin at databaseadvisors.com
: [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Susan
: Zeller
: Sent: Tuesday, March 04, 2003 3:14 PM
: To: dba-sqlserver at databaseadvisors.com
: Subject: RE: [dba-SQLServer]Stored Procedures
:
:
: Jim,
:
: I use UDF's for this.  Results of a UDF can be used like any table.
So,
: I massage data in a UDF which can receive parameters and then do the
: second manipulation in the sproc.  UDF's are only avaiable in 2000
: though.
:
: --Susan
:
:
: -----Original Message-----
: From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
: Sent: Tuesday, March 04, 2003 4:45 PM
: To: dba-sqlserver at databaseadvisors.com
: Subject: RE: [dba-SQLServer]Stored Procedures
:
:
: Hi All:
:
: Is there a way to use the results of one StoredProcedure to feed a
: second SP. The example I am working with is one SP to massages the
data
: and the second one manipulates the data via Transpose/Pivot.
:
: TIA
: Jim


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com