SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic creation of Insert, Update, Delete Stored procedure


Dynamic creation of Insert, Update, Delete Stored procedure

Author
Message
adi - doccolabs.com
adi - doccolabs.com
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 180
Nice article.
Wouldn't you rather use MERGE instead?
megasoft2
megasoft2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 3
truth, it does not work well with any table!

this is the message:

Procedure [dbo].[sp_iv00103_insert] Created Successfully
Msg 8102, Level 16, State 1, Procedure sp_et_create_sps_for_table, Line 189
Cannot update identity column 'DEX_ROW_ID'.
Procedure [dbo].[sp_iv00103_delete] Created Successfully

thanks!
Adrian Hains
Adrian Hains
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 261
We have a very similar set of dynamic sproc creation sprocs at my work. I find them useful in a general sense but (1) hard to look at and (2) hard to conditionally customize. I think this is a good example of something that can be implemented with greater clarity in c#. You would want similar metadata queries to get the table information, but I think applying these to a template would look tons better in app code. It could be a clr UDF if you wanted to keep it in the database, but I think the ideal solution would be to query metadata with FOR XML and then pass that XML into a standalone app. I definitely have servers that I'm prohibited from enabling CLR on, but I can obviously still run the FOR XML query.
bkubicek
bkubicek
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3016 Visits: 812
There is an issue with your proc. You use varchar(1000) and varchar(2000), the problem with this is with large tables this is not large enough. I changed all of those to varchar(max) and it works fine.

One more thing missing is a select. You have the insert, update and delete, but select is also needed.

Ben
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 979
... or see http://www.ssmstoolspack.com/Main.aspx
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62116 Visits: 19101
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: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Douglas Osborne-456728
Douglas Osborne-456728
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 327
Or

IF OBJECT_ID('dbo.SPName', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPName]
END
lexter_jj_lia
lexter_jj_lia
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 22
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
Mark-527630
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 167
The current version of the script needs an identity column in the table.
Adam Gojdas
Adam Gojdas
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1429
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search