Dynamic creation of Insert, Update, Delete Stored procedure

Question

Post reply

Dynamic creation of Insert, Update, Delete Stored procedure

Viewing 15 posts - 16 through 30 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

there are places for improvement, and the code in the article was updated in an earlier comment. I have updated the article to reflect that.

I think SELECT is a good thing to add. I have added 2 SELECT procs in my own generation procs before. One to return all values, and one to return a specific value. I've had the need for both those procs in many tables.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 2

Douglas Osborne-456728

SSCommitted

Points: 1569

July 23, 2009 at 12:34 pm

#1029192

Or

IF OBJECT_ID('dbo.SPName', 'P') IS NOT NULL

BEGIN

DROP PROCEDURE [dbo].[SPName]

END

lexter_jj_lia Newbie Points: 7 More actions · Answer 3

Hi there,

I tried to execute your stored procedure, no error, but didn't create stored procedures for Insert, update, and delete. From your first version of your stored procedure, I got an error for insert and update. The errors said 'Must declare the scalar variable "@VariableName". ' The deleted sp was created sucessfully. Any idea? thanks

AK

Mark-527630 SSC Enthusiast Points: 160 More actions · Answer 4

Mark-527630

SSC Enthusiast

Points: 160

July 23, 2009 at 3:09 pm

#1029303

The current version of the script needs an identity column in the table.

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 5

ddunn (7/23/2009)

I use SQL Admin Studio as it has a customizable template engine (I used it to write Delphi code to access stored procedures). It adds the following code to the top of the generated scripts so you don't have to worry about whether the stored procedure exists or not. Note that it creates the script in an editor so you can work with it before executing it.

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MyStoredProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[MyStoredProcedure] GO

You may even want to try something along these lines for the script. It avoids the drop which can cause auditing and/or permission issues. This method basically lets it run every time whether it exists or not.:



IF OBJECT_ID(N'[dbo].[Proc_Name]') IS NULL

BEGIN

    EXEC ('CREATE PROCEDURE

           [dbo].[Proc_Name]

           AS BEGIN SELECT ''STUB'' END');

END;

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Proc_Name]

AS

rest of code....

Also as said before the procs should NOT be named with an "sp_". Will cause a performance hit.

Deepthi Viswanathan Nair Old Hand Points: 392 More actions · Answer 6

Deepthi Viswanathan Nair

Old Hand

Points: 392

July 23, 2009 at 9:35 pm

#1029443

thanks for ur comment.. i would change it..

regards

deepthi

Kindest Regards,

Deepthi Viswanathan Nair

Sudarsan Srinivasan Ten Centuries Points: 1140 More actions · Answer 7

Sudarsan Srinivasan

Ten Centuries

Points: 1140

July 24, 2009 at 12:51 am

#1029491

Nice effort, and thanks for sharing it across.

dungimon Valued Member Points: 71 More actions · Answer 8

I work a system with hundreds of tables and we did have two stored procedures for each that were dynamically generated using our code generator. We've now moved away from this since the code foot print was huge and simple insert, update and delete statements could be better achieved in our c# code.

We have a code generator that creates a c# class (in our data layer) for evert table and then calling methods (that were dynamically generated based on table indexes and columns etc) this dynamically generates the sql and executes using sp_executesql. This change in how we execute these statements has been really well received, leaving the stored procedures for more complex tasks, reducing the vast no. of sp's we had and making it far more manageable.

Nice article though 🙂

nigel. SSChampion Points: 11636 More actions · Answer 9

There's also an issue when column names contain spaces, the stored procedure parameter names will also have the space in them and causes a syntax error.

Eg. Where a table has a column named 'Product Name' the following is generated:

CREATE PROCEDURE sp_something_insert



   @Product Name varchar(50)

AS

...

I realise it's not good practice to use spaces in column names but somone will.

Other odd characters are also potential hazards here.

Nigel
How to post questions

Deepthi Viswanathan Nair Old Hand Points: 392 More actions · Answer 10

Deepthi Viswanathan Nair

Old Hand

Points: 392

July 28, 2009 at 10:43 pm

#1031466

I never think about that.. it's a nice catch.. i would incorporate this ans update the sp.. thanks for ur comment..

Kindest Regards,

Deepthi Viswanathan Nair

blopezya Newbie Points: 9 More actions · Answer 11

blopezya

Newbie

Points: 9

August 27, 2009 at 1:26 pm

#1045825

Need to do replace('(' + Convert(varchar(10),syscolumns.length) +')','(-1)','(MAX)') to handle the varchar(max) datatype, wich shows as (-1) and screws the code...