Which is correct declaration of Stored Procedure - SQL Server

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
milani_lucieAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mankowitzConnect With a Mentor Commented:
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
0
 
jorgedeoliveiraborgesCommented:
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.
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END

is the correct syntax
0
 
FreelanzWithUsConnect With a Mentor Commented:
You should not use ( ) for stored procedures so the below will be correct

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

END
0
 
Kishan ZunjareSr. Software EngineerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.