Which is correct declaration of Stored Procedure - SQL Server

milani_lucie
milani_lucie used Ask the Experts™
on
Hi,

I can write the stored procedure in these ways:

CREATE PROCEDURE dbo.P(@X int, @Y int)
AS
BEGIN

END

CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END

Which one is correct syntax ? Which one needs to be used when ?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
for CREATE PROCEDURE you should not use parenthesis, but for CREATE FUNCTION, you should.

See the difference between

http://msdn.microsoft.com/en-us/library/ms187926.aspx

and

http://msdn.microsoft.com/en-us/library/ms186755.aspx
After the creation of the procedure P (with the first syntax), try to re-generate the scripts within «SQL Server Management Studio».
Do the same with the second syntax.

Compare the results.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END

is the correct syntax
You should not use ( ) for stored procedures so the below will be correct

CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END
Kishan ZunjareSr. Software Engineer

Commented:
hi,

A stored procedure or in simple a proc is a named SQL block which performs one or more specific task. This is similar to a procedure in other .NET languages.
A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE PROCEDURE dbo.sp_TestProc
AS
    SELECT FirstName, LastName FROM Person.Person;
GO


Syntax to Execute Stored procedure
EXEC sp_TestProc;
EXEC dbo.sp_TestProc;
GO

Syntax to delete Stored procedure
DROP PROCEDURE dbo.sp_TestProc;
GO

Let me know if you need more details on this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial