How to create New Store procedure(sp) in sql server(2005,2008):

Creating store procedure in sql server  is a simple task .The only thing you should be worried about is its syntax.

The syntax of store procedure(sp) is given below:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN

    --Write your query in this section (select,update,delete)
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

Examples:

Approach 1:

This is simple way to create a store procedure in sql server .Just write the store procedure(sp) name and define the input parameter  and query and your store procedure creation task is done.

CREATE PROCEDURE [dbo].[SpUserLogin]

    --Input Parameter
     @emailid varchar(100),@password varchar(100)

AS
BEGIN

      select * from TblUser where emailid=@emailid and password=@password

END

Approach 2:

In this Example we are checking the email id and password in table and if the user info exists we are passing the message as 'Login Successful' id not then 'User Does not Exists' in return from Sp(store procedure). 

CREATE PROCEDURE [dbo].[SpUserLogin]

    --Input Parameter
     @emailid varchar(100),@password varchar(100)

AS
BEGIN

    if exists(select emailid from dbo.tbluser where emailid=@emailid and password=@password)
    begin

     select 'Login Successfull'

    end

    else

    select 'User not exists'

END