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

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 a...
Post a Comment
Read more

Making Qunit show passing tests all the time.

So I have recently started testing my JavaScript with Qunit. Yeah! I looked at Jasmine but I just liked QUnit better except for one thing. I wanted to see all the passing tests all the time. I mean you work hard to get all you code properly tested and structured you want some positive feedback right? Qunit doesn’t show your tests unless you fail – bah! If you grab Qunit from GIT you can see that there is a config section at like, line 570 and this look like a great spot to have an option that would let you always show the passing tests in expanded form or whatever but there isn’t an option for this so I you need to add a hack for it. At (or near) line 210 you will find the following. That's it really - just save and you will have a screen full of happy positive feedback all the time. I mean if you have like fatty 500+ scripts it might start to get to be too much but I just wanted people to know who to do this if the wanted the option. Yeah!
Post a Comment
Read more

Copying items to a output directory using post-build events

Certain times you are going to need to move things to some sort output directory after they are built, for whatever reason - maybe your program is looking for a list of modules that it will load when it fires up (you can do this with prism)? It's easy to do, just go to the "Properties" for your project and select "Build Events". in the box titled "Post-Build event command line" enter your xcopy command. Somthing like this. xcopy "$(TargetDir)PARTSFinderModule.dll" "$(SolutionDir)\MyProject\bin\$(PlatformName)\$(ConfigurationName)\DirectoryModules" /Y That's it.
Post a Comment
Read more