[dba-SQLServer]Stored Procedures

Chris Mackin chris at denverdb.com
Wed Mar 5 10:15:50 CST 2003


The key to remember Jm is that you need to create your Temp table in the
outermost level of nesting since the table will disappear after the sproc
that created it has completed execution.

Use a # in fronbt of the table name for creating and all references to it
(CREATE TABLE #MyTemp....)

Then in the nested sproc

INSERT INTO #MyTemp.......

In your main sproc you can use it in a SQL statement same as any other
table.

Chris Mackin
Denver Database Consulting, LLC
www.denverdb.com


-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Jim
Lawrence (AccessD)
Sent: Wednesday, March 05, 2003 9: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







More information about the dba-SQLServer mailing list