Which is the correct syntax for Stored procedure in SQL server ?

Question

which one is correct ??? 1. CREATE PROCEDURE procedure_name AS sql_statement GO; OR 2. CREATE PROCEDURE [schema_name].procedure_name @parameter_name data_type .... parameter_name data_type AS BEGIN -- SQL statements -- SELECT INSERT UPDATE or DELETE statement END

Brahma Prakash Shukla · Answer

The syntax to create a stored procedure in SQL Server (Transact-SQL) is: CREATE { PROCEDURE | PROC } [schema_name.] procedure_name [ @parameter [type_schema_name.] datatype [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]  @parameter [type_schema_name.]

Satya Karki · Answer

Hi both Syntaxes are correct Based on the need it depends on which one to use. The first one you can use when you don't need to pass parameters in the query such as SQL queries to select all values without where condition(s). The second one you can use when you need a parameterized query. You can check the below link for a sample. https://www.w3schools.com/sql/sql_stored_procedures.asp

Vishal Joshi · Answer

Hello Both syntaxes are correct. there are several properties we can configure during create procedure. like parameterize with output variable with NoCount etc. So it depends on the requirements we can write a stored procedure. below is the default syntax: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE -- Add the parameters for the stored procedure here =  = AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT  END GO

Vishal Yelve · Answer

CREATE PROCEDURE [schema_name].procedure_name @parameter_name data_type .... parameter_name data_type AS BEGIN -- SQL statements -- SELECT INSERT UPDATE or DELETE statement END

Rajeev Kumar · Answer

CREATE procedure [dbo].[Usp_GetArticleDealDiscount] @Articleid int AS Begin Declare @SubscriptionPublishedDate datetime@NetPriceAfterExchangeRate decimal(122)@JournalTypeid int -- SQL statements -- SELECT INSERT UPDATE or DELETE statement END