How to create Stored Procedure in SQL Server 2005 or 2008 ?

Solution :

You can directly copy below code highlight and paste in sql server query window and modify according to your need.

/**********************************************************************************
SP Name : XYZ_SP_ProgramOperation
Created by : Rajesh Singh
Created Date : 13th April 2010
Modified Date : 15th April 2010 
*********************************************************************************/

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID=OBJECT_ID('XYZ_SP_ProgramOperation'))
DROP PROCEDURE XYZ_SP_ProgramOperation
GO 
create PROCEDURE XYZ_SP_ProgramOperation
(
    @sOperation varchar(100),
    @iProgramID INT=NULL,
    @sProgramName varchar(100)=NUll,
    @sSlogan varchar(100)=NUll,
    @sStartTime varchar(100)=NUll,
    @sEndTime varchar(100)=NUll,
    @sDetail varchar(MAX)=NUll,
    @iCityID INT=NUll,
    @sStatus varchar(20)=NUll
    
)
AS
IF @sOperation='SELECT'
            BEGIN
            SELECT * FROM [XYZ_Program] WHERE sStatus='Active'
            END
ELSE IF @sOperation='SELECTPROGRAMBYID'
            BEGIN
            SELECT XYZ_Program.*, XYZ_Rj.sUserName,XYZ_Rj.sPhoto, XYZ_Rj.sRjName,(select sCityName from XYZ_City where iCityId=XYZ_Program.iCityID) as sCityName,XYZ_RjProgRel.iRjId
            FROM   XYZ_Program INNER JOIN
            XYZ_RjProgRel ON XYZ_Program.iProgramID = XYZ_RjProgRel.iProgramID INNER JOIN
            XYZ_Rj ON XYZ_RjProgRel.iRjId = XYZ_Rj.iRJid WHERE XYZ_Program.iProgramID=@iProgramID
            END
ELSE IF @sOperation='UPDATE'
            BEGIN
            UPDATE [XYZ_Program]
               SET [sSlogan] = @sSlogan
                  ,[sStartTime] = @sStartTime
                  ,[sEndTime] = @sEndTime
                  ,[sDetail] = @sDetail
                  ,[iCityID] = @iCityID
                  ,[sStatus] = @sStatus
             WHERE XYZ_Program.iProgramID=@iProgramID
            END


GO

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com