Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
milani_lucie
Asked:
milani_lucie
3 Solutions
 
mankowitzCommented:
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 PatelAssistant ConsultantCommented:
CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END

is the correct syntax
0
 
FreelanzWithUsCommented:
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 ZunjareCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now