SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Include Code in various stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/02/2006 :  21:59:42  Show Profile  Reply with Quote
I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?

shallu1_gupta
Constraint Violating Yak Guru

India
394 Posts

Posted - 02/02/2006 :  22:55:44  Show Profile  Reply with Quote
Hi,
There is no such INCLUDE statement in SQL server. However You can Create an Sp that Creates a #temp Table and return the same. you can execute the SP in rest of you stored procedure and get the output.
else explain your requirement.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/03/2006 :  07:51:38  Show Profile  Visit nr's Homepage  Reply with Quote
>> However You can Create an Sp that Creates a #temp Table and return the same.
Unfortunately that temp table will be out of scope when the sp returns and so not available.
It would need to be created from the calling sp then populated in a called sp.

It might be easier to include code in the sps when they are created - maybe by puttnig an identifier in the sp then processing the source file when it is applied to the server. You should be able to do this quite easily using osql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/03/2006 :  08:45:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Also, consider potentially writing that code as a UDF, if you can. Then the UDF just returns the rows to the stored proc and the proc can do whatever it wants with them (put them in a table table, return them directly, etc).

Or, instead of having the proc create a temp table directly, it could just return the results that normally go into the temp table, and the calling proc can put those results in the temp table. To me, this makes a lot more sense if you have to do it this way. You would just say:

exec 'Yourproc'
into #tmp

It might pay off to step back a bit and let us know what your "temp table creation" proc is doing and how you are using those results.

Edited by - jsmith8858 on 02/03/2006 08:46:43
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 02/03/2006 :  09:00:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
exec 'Yourproc'
into #tmp


Did you mean this?

Insert into #tmp(columns) exec Yourproc


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 02/03/2006 09:00:32
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/03/2006 :  10:38:03  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by madhivanan

quote:
exec 'Yourproc'
into #tmp


Did you mean this?

Insert into #tmp(columns) exec Yourproc


Madhivanan

Failing to plan is Planning to fail



yeah, that's it ... must've been a typo!
(I always forget the syntax for that one, thanks Madhivanan!)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/03/2006 :  12:31:26  Show Profile  Reply with Quote
Does anyone to to offer that this sounds like a bad idea in the first place?

OK, I will.

This sounds like a bad idea.

What are you trying to accomplish?

Sounds like a chnage to the model would better suit you.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/03/2006 :  16:28:48  Show Profile  Reply with Quote
I have a monthend routine that does a number of calculations and new record creations. To accomplish this, I create a #Temp file. I then use this file to Update the Monthend Data, or give them a Preliminary Report, or it can be used in other reports as well. I like the idea of having the code in 1 procedure rather than duplicating the code. Changes do happen frequently and so I want to be able to just change the 1 procedure.
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/04/2006 :  10:45:49  Show Profile  Reply with Quote
It's not clear to me why this would be a bad idea. Can you elaborate.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/04/2006 :  11:00:49  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Well, as mentioned -- I don't think you should have a stored procedures "output" be putting stuff into a temp table. You might want the stored procedure to RETURN the rows instead of dumping them into a temp table; that way, whatever calls the stored proc can do whatever it wants with the results, whether it is returning them directly, or putting them into a temp table local to the *calling* stored proc, which would then be in scope.

If stored proc A calls stored proc B, and stored proc B creates a temp table and returns, stored proc cannot access that temp table and, in fact, it has probabyl already been deleted by SQL Server. BUt if stored proc A calls stored proc B, and stored proc B *returns* the results to stored proc A, then stored proc A can do whatever it wants with those results.

Edited by - jsmith8858 on 02/06/2006 08:28:43
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/06/2006 :  10:05:04  Show Profile  Reply with Quote
Maybe if you can suppply some sample code it might help.

Read the hint link in my sig



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/07/2006 :  16:21:36  Show Profile  Reply with Quote
Here's some sample code as requested. The procedure is incomplete as there are more calculations to follow, but this gives you an idea.
CREATE PROCEDURE dbo.pr_incChemUsages
AS
SET NOCOUNT ON
/*
	++++++++++++++++++++++++++++++++++++++++
	Chemical Usages
	++++++++++++++++++++++++++++++++++++++++ 
*/
Declare @XFromDate datetime
Declare @XToDate datetime

SELECT @XFromDate = DateAdd(m,-1,ChemicalPeriod),
	@XToDate = ChemicalPeriod - 1
FROM tblCtrl
WHERE CtrlID = 'CTRL'

/*
	++++++++++++++++++++++++++++++++++++++++
	Pickup Monthend records
	++++++++++++++++++++++++++++++++++++++++ 
*/
SELECT 
	QChemical = Left(ProductID, 7),
	QSource = 'tblMonthend',
	QUsageDols = UsageDols,
	QUsageQty = UsageQty
INTO #Temp
FROM tblMonthend
WHERE MEPeriod = @XFromDate And (UsageDols <> 0 Or UsageQty <> 0)

UNION ALL
SELECT 
	'1215490B',
	'tblUsages',
	0,
	ChemUsage / 1000
FROM tblUsages
WHERE ChemPeriod = @XFromDate And ChemicalID = 'TURBOTAK'

UNION ALL
SELECT 
	'TOILSALE',
	'CALC',
	0,
	0
/*
	++++++++++++++++++++++++++++++++++++++++
	Calculate Lime to Effluent	
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	'LIMEEFFL',
	'CALC',
	QLimeEff = CASE 
		WHEN TEMP.UsageQty = 0 THEN 0 
		ELSE Round(ChemUsage * Round(TEMP.UsageDols / TEMP.UsageQty, 2), 0) END,
	-1 * ChemUsage
FROM tblUsages USAG
LEFT OUTER JOIN 
(
	SELECT
		MEPeriod,
		UsageDols,
		UsageQty		
	FROM tblMonthend
	WHERE MEPeriod = @XFromDate And ProductID = '1218260'
) TEMP ON USAG.ChemPeriod = TEMP.MEPeriod
WHERE ChemicalID = 'LIMETFR' AND ChemPeriod = @XFromDate
/*
	++++++++++++++++++++++++++++++++++++++++
	Calculate 'Acid to Ponds' and 'Tall Oil'	
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	QChemical = CASE WHEN AdjComments like '%PONDS%' THEN 'ACIDPOND' ELSE 'ACIDTOIL' END,
	'CALC',
	QUsageDols = Sum(-1 * AdjAmount),
	QUsageQty = Sum(-1 * AdjQty)
FROM tblAdjustments
WHERE ProductID = '1218910' And AdjDate Between @XFromDate And @XToDate 
	And (AdjComments like '%PONDS%' Or AdjComments like '%TALL OIL%')
GROUP BY CASE WHEN AdjComments like '%PONDS%' THEN 'ACIDPOND' ELSE 'ACIDTOIL' END 
/*
	++++++++++++++++++++++++++++++++++++++++
	Select Oxygen And Acid Records from Usage file
	Use Rates from Monthend file 
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	USAG.ChemicalID,
	'CALC',
	QDols = CASE
		WHEN USAG.ChemicalID IN ('1218050K','1218050D','1218050E') THEN Round(ChemUsage / 1000 * QO2Rate, 2)
		WHEN USAG.ChemicalID IN ('BWACID','PHACID') THEN Round(ChemUsage / 1000 * QAcidRate, 2)
		ELSE 0 END,
	ChemUsage / 1000
FROM tblUsages USAG
JOIN (
	SELECT MEPeriod,
		QO2Rate = Sum(CASE WHEN ProductID = '1218050' THEN UsageDols / UsageQty ELSE 0 END),
		QAcidRate = Sum(CASE WHEN ProductID = '1218910' THEN UsageDols / UsageQty ELSE 0 END)
	FROM tblMonthend
	WHERE MEPeriod = @XFromDate And ProductID IN ('1218050','1218910')
	GROUP BY MEPeriod
) J1 ON USAG.ChemPeriod = J1.MEPeriod
WHERE USAG.ChemicalID IN ('1218050K','1218050D','1218050E','BWACID','PHACID') And USAG.ChemPeriod = @XFromDate 
	And USAG.ChemUsage is not Null And USAG.ChemUsage <> 0 
/*
	++++++++++++++++++++++++++++++++++++++++
	Deduct BWACID from 1218910 Product
	++++++++++++++++++++++++++++++++++++++++ 
*/
INSERT INTO #Temp (QChemical, QSource, QUsageDols, QUsageQty)
SELECT
	'1218910',
	'CALC',
	-1 * QUsageDols,
	-1 * QUsageQty
FROM #Temp
WHERE QChemical = 'BWACID'
/*
	++++++++++++++++++++++++++++++++++++++++
	Rounding - 1218050 + 1215490
	++++++++++++++++++++++++++++++++++++++++ 
incomplete
*/ 
SELECT * 
FROM #Temp
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/10/2006 :  14:58:27  Show Profile  Reply with Quote
Decided to go ahead with the INSERT command as suggested by jsmith. Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000