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

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

Connecting to a HiTechnic prototype board to an Arduino

Connecting to a HiTechnic prototype board to an Arduino. If you are thinking to yourself, “ Wouldn't it be fun to take a prototype board from Hitechnic and connect it to my Arduino? I wonder if it is possible...” Well I am here to tell you that Yes, indeed it is possible, it is not only possible it works rather nicely, of course - HiTechnic doesn’t really support this and the NXT documentation doesn’t have a section called “Cutting a cable in half to connect it your Arduino” so it took a little research to make it it happen which is why I thought I would share this information with the world. Step one - the cable  I took a cable from my mindstorms kit and chopped one end off, I then took some nice stiff jumper wires and soldered them onto the ends so I had something that I could plug into the Arduino.  Step two - What goes where.  So the big question was what pins to plug it into on the Arduino. I have an Uno which means that pins A4 and A5 are the i2c SDA and S
Post a Comment
Read more