Join 2 stored procedures who has parameter 1

[IcesMaster]

I have 2 procedures (functions) each having parameter (@...). How to joint it?
Thanks

 

[ClaireHCM]

Use one proc to call the other by passing additinol parameters that are needed for executing the proc.
Say

Create proc proc1 @id int
as
if @id = 1
select * from authors
if @id = 2
select * from titles

create proc proc2 @name varchar(10),@id int
execute proc1 @id

Just an example!

 

[bertrandkis]

There is possiblity to help you but I do not quite understand what you mean by joining the functions or stored procedures. Could you elaborate more on what you want to do?
Regards

 

[IcesMaster]

Thanks Claire
I already found that I made too much parameter so while I create the procedure to joint the functions I made mistake in writing the parameter structure.

For Bertrand, here I made the sample of Joining parameter function in stored procedure as I mean

Exp. I have two functions:

ALTER FUNCTION CurrentTransaction(@Year smallint, @Month SmallInt)
AS
SELECT dbo.MyTransaction.Account, Sum(MyTransaction.Amount As CurMonth,
FROM dbo.MyTransaction
HAVING Year(Date) = @Year AND Month(Date) = @Month
GROUP BY dbo.MyTransaction.Account


ALTER FUNCTION CummulativeTransaction(@Year smallint, @Month SmallInt)
AS
SELECT dbo.MyTransaction.Account, Sum(MyTransaction.Amount As CumMonth,
FROM dbo.MyTransaction
HAVING Year(Date) = @Year AND Month(Date) <= @Month
GROUP BY dbo.MyTransaction.Account

The stored Procedure:

ALTER PROCEDURE ReportData(@Year Smallint, @Month Smallint)
AS
SELECT dbo.CurrentTransaction.Rekening, CurrentTransaction.CurMonth, dbo.CummulativeTransaction.CumMonth,
FROM dbo.CurrentTransaction(@Year, @Month) INNER JOINT
dbo.CummulativeTransaction(@Year, @Month) ON dbo.CurrentTransaction.Account = dbo.CummulativeTransaction.Account

Thanks for your attention too
Best Regards

 

[bertrandkis]

Here is what I think you should do. Use INLINE TABLE-VALUED FUNCTION this means functions that return tables as an output. So your function definition should look like this:
ALTER FUNCTION CurrentTransaction(@Year smallint, @Month SmallInt)
RETURNS TABLE
AS
RETURN(
SELECT dbo.MyTransaction.Account, Sum(MyTransaction.Amount) As CurMonth
FROM dbo.MyTransaction
GROUP BY dbo.MyTransaction.Account
HAVING Year(Date) = @Year AND Month(Date) = @Month)

ALTER FUNCTION CummulativeTransaction(@Year smallint, @Month SmallInt)
RETURNS TABLE
AS
RETURN(SELECT dbo.MyTransaction.Account, Sum(MyTransaction.Amount) As CumMonth
FROM dbo.MyTransaction
GROUP BY dbo.MyTransaction.Account
HAVING Year(Date) = @Year AND Month(Date) <= @Month)
Notice that the Having Clause always comes after the Group by clause,also notice the returns keyword used to return the table.
This should work. Please come back here if you still have a problem.

 

[IcesMaster]

Thank you