Calling a stored procedure from another procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling a stored procedure from another procedure

Author

Topic

June
Starting Member

18 Posts

Posted - 2008-12-30 : 04:44:57

Hi,
I am developing a material management system for a company. While preparing indent, the indentor needs to know the last three year's consumption for all the materials of the indent. The indentor gets the consumptions from the following table:

IssueDetails

SIV IssueDate MaterialCode IssueQuantity

Say, I am passing the following values to this table:
MaterialCode: EL3712 and MaterialCode: DG5881
Current Date: 30-12-2008
Now from the IssueDetails table, how can I get a table like this:

TempTable

MaterialCode Year1 Year2 Year3
EL3712 QuantityYear1 QuantityYear2 QuantityYear3
DG5881 QuantityYear1 QuantityYear2 QuantityYear3

I have a stored procedure which gives me the indented materials and their present balance.
Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 04:52:10

i think its better to make it a table valued user defined function rather than procedure. this will provide you with a table with reqd resultset when you call function from your procedure after passing reqd parameter values.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-30 : 11:04:19

...or a view, if present balance can be calculated in a single statement.
June, post the code for your stored procedure.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

June
Starting Member

18 Posts

Posted - 2008-12-31 : 01:42:20

OK Guys..I am giving you all..First the required tables..
Indent

IRN IndentDate DepartmentName SectionName Status

Material

Code Description UOM Balance

IndentMaterial

IRN MaterialCode IndentedQuantity Balance OnOrdered ToBeOrdered

Here IRN is Indent Reference No. which is unique for an indent. Now the stored procedure:

CREATE PROCEDURE MMEntry @IRN varchar(50)
AS
SET NOCOUNT ON

SELECT MaterialCode, Description, UOM, IndentedQuantity,Balance,ToBeOrdered
FROM IndentMaterial IM, Material
WHERE IM.IRN = @IRN
AND IM.MaterialCode = Material.Code
GO

Now I need to join this with the Temporary table that the user defined function is supposed to generate. Then I can display the Item Code,their Description and their last three year's consumption in a DataGridView. If the current date is 31-12-2008, then the consumptions for 2006,2007 and 2008 needs to be generated.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-31 : 10:06:28

Seems like you might be able to just write this as a view without the parameter and apply the filter against IRN when you join it. There aren't any difficult calculations involved and it is a single-step script.
But if you can't, then go ahead and use a user-defined table function. Though I will caution you that these can be inefficient if the result set they return is even moderately large. How many records do you expect to get back from the MMEntry procedure?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

June
Starting Member

18 Posts

Posted - 2009-01-02 : 00:23:02

Hi blindman,thanks for the reply. From the MMEntry procedure I expect to get back at most six records. So I think inefficiency should not be a concern. I am a novice in sql and have never used UDFs before. Could you please show me how to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:25:14

quote:
Originally posted by June

Hi blindman,thanks for the reply. From the MMEntry procedure I expect to get back at most six records. So I think inefficiency should not be a concern. I am a novice in sql and have never used UDFs before. Could you please show me how to do it?

refer below to see different types of UDFs

http://www.sqlteam.com/article/user-defined-functions

June
Starting Member

18 Posts

Posted - 2009-01-02 : 01:49:26

thanks visakh. I got the idea of the UDF that will extract the consumptions from the IssueDetails table. But how can I pass the MaterialCodes and the years from the MMEntry procedure to the UDF. Again if the entry date is 02-01-2009, should I use a loop to get the consumptions for years 2006,2007,2008? If you show it with some sql code, maybe I can take it onwards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:59:31

you can pass it as a comma seperated list if you want to get data for multiple years. then in UDF, write logic to parse the comma seperated list and get individual values and use it to get your reqd data.

June
Starting Member

18 Posts

Posted - 2009-01-02 : 06:05:18

Hi guys...this is what I have done till now...

CREATE PROCEDURE MMEntry
@IRN varchar(50),
@Year1 varchar(50),
@Year2 varchar(50),
@Year3 varchar(50),

AS
SET NOCOUNT ON
DECLARE @List varchar(2000)

SELECT @List = COALESCE(@List + ',', '') + Cast(MaterialCode As varchar(9))
FROM IndentMaterial IM, Material
WHERE IM.IRN = @IRN
AND IM.MaterialCode = Material.Code

SET @List = @List+',' + @Year1 + ',' + @Year2+ ',' + @Year3

I will send this list to another procedure. There I will parse the list and input the Material codes in a temporary table. After that I am clueless... To get the consumptions for all 3 years, should I use some kind of loop that will run for each of the materials?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 06:39:49

for getting consumptions for 3 years, isnt it enough just to pass startdate and enddate for 3 year period and get the data for the full period between them. you may group on year if you need yearwise data for analysis.

June
Starting Member

18 Posts

Posted - 2009-01-05 : 02:35:45

Hi all...this is the query i used to get the consumptions for last three years..

SELECT MaterialCode,
Sum(Year1) AS [First Year]
,Sum(Year2) AS [Second Year]
,Sum(Year3) AS [Third Year]
FROM (
SELECT MaterialCode
, CASE
WHEN IssueDate >= convert(datetime, '01/04/2004', 105) and IssueDate < convert(datetime,'31/03/2005' , 105)
THEN IssueQuantity
ELSE 0
END AS [Year1]
, CASE
WHEN IssueDate >=convert(datetime, '01/04/2005', 105) and IssueDate < convert(datetime,'31/03/2006', 105)
THEN IssueQuantity
ELSE 0
END AS [Year2]
, CASE
WHEN IssueDate >= convert(datetime, '01/04/2006', 105) and IssueDate < convert(datetime, '31/03/2007', 105)
THEN IssueQuantity
ELSE 0
END AS [Year3]
FROM IssueDetails IDT

INNER JOIN Material ON IDT.MaterialCode = Material.Code ) AS InnerDetail
GROUP BY MaterialCode)

Now how can I join these values with the MMEntry procedure to get the result in a datagridview like this:

MaterialCode Description UOM IndentedQuantity Year1 Year2 Year3
Should I use a temporary table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources