Solved

Which is correct declaration of Stored Procedure - SQL Server

Posted on 2011-09-05
5
239 Views
Last Modified: 2012-06-27
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
Comment
Question by:milani_lucie
5 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 167 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
Comment Utility
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
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 167 total points
Comment Utility
CREATE PROCEDURE dbo.P
@X int,
@Y int
AS
BEGIN

END

is the correct syntax
0
 
LVL 1

Assisted Solution

by:FreelanzWithUs
FreelanzWithUs earned 166 total points
Comment Utility
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
 
LVL 7

Expert Comment

by:Kishan Zunjare
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now