Wednesday, March 11, 2009

My stored procedure template

OK - It is really just a nice example that you can cut and paste and customize... You get the point though!

If you use MSSQL you probably know that you can right click on the stored procedures folder and click "new stored procedure" - this gives you a fairly workable template but it uses the CREATE syntax, then you have to change that to ALTER when you make a change to the proc. This can become a real pain if several folks are trying to run this on different development databases, then run it out the production server / test server, whatever, you get the point... The template below lets you modify and run the procedure as many times as you like and wherever you like with no regard for weather or not the procedure exists.

It also grants permissions for who can run this proc, you can adjust that as you please.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- ============================================================================

-- Create date:

-- Description:

============================================================================

USE [AdventureWorks]

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsertContact')

BEGIN

PRINT 'Dropping Procedure InsertContact'

DROP Procedure InsertContact

END

GO

PRINT 'Creating Procedure InsertContact'

GO

CREATE Procedure InsertContact

@CorporateAccountId int,

@Title varchar(10),

@FirstName varchar(50),

@LastName varchar(50),

@ProfessionalTitle varchar(100),

@Company varchar(100),

@CompanyWebUrl varchar(100),

@Industry varchar(10),

@ContactAddress1 varchar(50),

@ContactCity varchar(50),

@ContactPostalCode varchar(50),

@ContactCountryRegionId int,

@ContactState varchar(100) = null,

@Email varchar(50),

@Telephone varchar(50),

@Fax varchar(50),

@Username varchar(256),

@Password varchar(50),

@SecretQuestion varchar(100),

@SecretAnswer varchar(100),

@CreatedBy int,

@ModifiedBy int,

@ContactTypeId char(1),

@ReferrerId varchar(25),

@ReferrerHost varchar(100),

@Id int OUTPUT

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT Id

FROM [dbo].[Users]

WHERE Username=@Username)

BEGIN

SELECT @ID = (SELECT 0)

RETURN @ID

END

INSERT INTO [dbo].[Contact]

(

CorporateAccountId,

Title,

FirstName,

LastName,

ProfessionalTitle,

Company,

CompanyWebUrl,

Email,

Telephone,

Fax,

SecretQuestion,

SecretAnswer,

ContactTypeId,

Active,

IndustriesForContactsId,

ReferrerId,

ReferrerHost,

CreatedOn,

CreatedBy,

ModifiedOn,

ModifiedBy

)

VALUES

(

@CorporateAccountId,

@Title,

@FirstName,

@LastName,

@ProfessionalTitle,

@Company,

@CompanyWebUrl,

@Email,

@Telephone,

@Fax,

@SecretQuestion,

@SecretAnswer,

@ContactTypeId,

1,

@Industry,

@ReferrerId,

@ReferrerHost,

getdate(),

@CreatedBy,

getdate(),

@ModifiedBy

)

SELECT @Id = @@Identity

INSERT INTO [dbo].[Address]

(

ContactId,

AddressTypeId,

Address1,

City,

State,

PostalCode,

CountryRegionId

)

VALUES

(

@Id,

'B',

@ContactAddress1,

@ContactCity,

@ContactState,

@ContactPostalCode,

@ContactCountryRegionId

)

INSERT INTO [dbo].[Users]

(

ContactId,

Username,

Password,

CreatedOn,

CreatedBy,

ModifiedOn,

ModifiedBy

)

VALUES

(

@Id,

@Username,

@Password,

getdate(),

@CreatedBy,

getdate(),

@ModifiedBy

)

END

GO

GRANT EXEC ON InsertContact TO PUBLIC

GO


No comments:

Post a Comment

Followers