Skip to main content

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


Comments

Post a Comment

Popular posts from this blog

Unit testing static methods when using Membership and ProfileBase in MVC 4

So you might be thinking that you want to use the Membership class and the ProfileBase class in Microsoft’s System.Web.Security and System.Web.Providers but then you also want to write unit tests as well so you decide to go stand in traffic instead once you are overcome by the plethora of static nonsense that is the design of these two classes. OK, so it isn’t that bad but it is frustrating however there are ways around it and I wanted to try and blog about some of those techniques in the hope that others won’t have to toil with as much frustration as I did when using these classes. Backgound Basically I wanted to find a clean simple way of making use of and extending Microsofts built in forms authentication. It’s easy to use, relatively secure and can save a lot of time and code, well sort of until you want to unit test. The ProfileBase class is great for extending profiles and adding custom properties so you don’t have to introduce a whole lot of redundant code. A good example of...
Post a Comment
Read more

Musings on using a module catalog with Prism

anyone using prism is probably familar with the bootstrapper, the documentation defines the bootstrapper as a class responsible for initialization of an application built using Prism and if you dig into the code for it you will see lots of virtual methods that you can override when setting up your application, one of those is the CreateModuleCatalog(). There are several ways to initialize your modules but using an xaml file is incredibly convenient especially if you only want certain parts of your application to load under certain conditions. Where I work we recently decided to employ this feature so that we could load a subset of the application in the warehouse and not have the sales and other modules loading up at run time, on the flip side the warehouse module doesn't load when the sales team loads the application. The great part for the developer is you don't end up with multiple code bases, your core and infrastructure is shared in one application and different people see...
Post a Comment
Read more

Styling the combox box in WPF

If you want to play with the style of the combo box in WPF the easiest thing to do is use expression blend, drag a combo box control onto you project then click, "Edit Template" -> "Edit a copy"... This will take the entire default template for the combox box and put it into your XAML file. Now go to the XAML and you can grab the entire style and do whatever you like with it... I usually put things like this into a main poject under the solution, something like "shared" is a good name for the project... One thing that is important to note though is that you have to include a reference to the PresentationFramework.Aero assembly cause you will need the Microsoft.Windows.Themes namespace.
Post a Comment
Read more