can we have many procedure in single stored proced

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

General SQL Server Forums

New to SQL Server Programming

can we have many procedure in single stored proced

Author

Topic

seeanj1
Starting Member

3 Posts

Posted - 2011-04-12 : 12:37:19

I am new to sql server and writing stored procedure. I am wondering just like in oracle package can we have many procedure in single stored procedure.

If it is then can you please send me the sample code for this.

If it is not then what is the alternative and how to call a function and stored procedure in the main procedure. Give me an example please.

Thank you for your help.

Anjan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 12:54:50

There's a deprecated feature of SQL Server that allows you to create numbered procedures:

CREATE PROCEDURE #a AS
PRINT GETDATE();
GO
CREATE PROCEDURE #a;2 AS
PRINT GETDATE()+1;
GO

EXEC #a
EXEC #a;2

DROP PROCEDURE #a

However this feature will be removed and is not recommended. There's no practical benefit except you can drop all the numbered procedures by dropping the main one.

As far as calling a function from a stored procedure, the example code does just that. We'll need more details if this doesn't answer your question.

seeanj1
Starting Member

3 Posts

Posted - 2011-04-12 : 13:00:19

Thank you for the reply.

I am trying to write a big stored procedure and might have many function or procedure I want to use.

Smaller function and procedure will return me values and I want to use it in the main procedure.

Example

P1 return Value1
F1 return Value2
P2 return Value3

Main Procedure() will be using P1, F1 and P2 and do the necessary action.

Can We do this in one big stored procedure or I need to create it individually.

Thank you,
Anjan

quote:
Originally posted by robvolk

There's a deprecated feature of SQL Server that allows you to create numbered procedures:
CREATE PROCEDURE #a AS
PRINT GETDATE();
GO
CREATE PROCEDURE #a;2 AS
PRINT GETDATE()+1;
GO

EXEC #a
EXEC #a;2

DROP PROCEDURE #a
However this feature will be removed and is not recommended. There's no practical benefit except you can drop all the numbered procedures by dropping the main one.

As far as calling a function from a stored procedure, the example code does just that. We'll need more details if this doesn't answer your question.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 13:13:46

If you reuse the P1, P2 and F1 procedures in multiple places then it's better to keep them separate. But if they're only used for this one main procedure, it might be easier to combine them. There's no difference in performance.

Here's an example of calling multiple procedures within the main (these are all simplified):

CREATE PROCEDURE #p1 @p1 DATETIME OUTPUT AS
SET @p1=GETDATE()
GO

CREATE PROCEDURE #p2 @p2 DATETIME OUTPUT AS
SET @p2=GETDATE()+10
GO

CREATE PROCEDURE #f1 AS
RETURN 10000;
GO

CREATE PROCEDURE #main AS
DECLARE @p1_result DATETIME, @f1_result INT, @p2_result DATETIME

EXEC #p1 @p1_result OUTPUT	-- returns datetime so must use output variable

EXEC @f1_result = #f1		-- returns integer so can use return value

EXEC #p2 @p2_result OUTPUT	-- returns datetime so must use output variable

SELECT @f1_result F1_Result, 
DATEDIFF(SECOND, @p1_result, @p2_result) Seconds,
DATEADD(SECOND, @f1_result, @p1_result) AddedSeconds

GO

EXEC #main

DROP PROCEDURE #main, #p1, #p2, #f1

seeanj1
Starting Member

3 Posts

Posted - 2011-04-12 : 13:55:13

Thank you. I will try this and I think this should work fine.
Anjan

quote:
Originally posted by robvolk

If you reuse the P1, P2 and F1 procedures in multiple places then it's better to keep them separate. But if they're only used for this one main procedure, it might be easier to combine them. There's no difference in performance.

Here's an example of calling multiple procedures within the main (these are all simplified):
CREATE PROCEDURE #p1 @p1 DATETIME OUTPUT AS
SET @p1=GETDATE()
GO

CREATE PROCEDURE #p2 @p2 DATETIME OUTPUT AS
SET @p2=GETDATE()+10
GO

CREATE PROCEDURE #f1 AS
RETURN 10000;
GO

CREATE PROCEDURE #main AS
DECLARE @p1_result DATETIME, @f1_result INT, @p2_result DATETIME

EXEC #p1 @p1_result OUTPUT -- returns datetime so must use output variable

EXEC @f1_result = #f1 -- returns integer so can use return value

EXEC #p2 @p2_result OUTPUT -- returns datetime so must use output variable

SELECT @f1_result F1_Result,
DATEDIFF(SECOND, @p1_result, @p2_result) Seconds,
DATEADD(SECOND, @f1_result, @p1_result) AddedSeconds

GO

EXEC #main

DROP PROCEDURE #main, #p1, #p2, #f1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources