How to Write Stored Procedure

Hello all,
Today we are going to learn how to write a stored procedure in sql server. The following code is a sample one. If you like it you can use this format for all stored procedure…

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists(select * from dbo.sysobjects where id=object_id(N'[dbo].[StoredProcedureName]') and OBJECTPROPERTY(id,N'IsProcedure') == 1)
drop procedure [dbo].[StoredProcedureName]
GO

CREATE PROCEDURE [dbo].[StoredProcedureName]
@prmParameter int
with encryption
AS
declare @ID as nvarchar(4)

Select * from tableName where id = @prmParameter

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Now if you want to execute the sp please write the following command..

exec StoredProcedureName 10

here 10 is the prmParameter value..

thats it…

Thanks..

About these ads