Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Dynamic creation of Insert, Update, Delete Stored procedure Expand / Collapse
Author
Message
Posted Thursday, July 23, 2009 7:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 7, 2014 4:08 PM
Points: 55, Visits: 169
Nice article.
Wouldn't you rather use MERGE instead?
Post #758188
Posted Thursday, July 23, 2009 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 9:57 PM
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!
Post #758201
Posted Thursday, July 23, 2009 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 2012 1:18 PM
Points: 110, 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.
Post #758210
Posted Thursday, July 23, 2009 8:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:38 AM
Points: 1,824, Visits: 479
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
Post #758238
Posted Thursday, July 23, 2009 8:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 984, Visits: 779
... or see http://www.ssmstoolspack.com/Main.aspx
Post #758254
Posted Thursday, July 23, 2009 9:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
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
Post #758310
Posted Thursday, July 23, 2009 12:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Or

IF OBJECT_ID('dbo.SPName', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPName]
END
Post #758491
Posted Thursday, July 23, 2009 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 9, 2010 3:25 PM
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
Post #758544
Posted Thursday, July 23, 2009 3:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 26, Visits: 107
The current version of the script needs an identity column in the table.
Post #758623
Posted Thursday, July 23, 2009 3:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
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.
Post #758628
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse