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 9:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 12:49 PM
Points: 8, Visits: 25
thanks for ur comment.. i would change it..

regards
deepthi



Kindest Regards,

Deepthi Viswanathan Nair

Post #758789
Posted Friday, July 24, 2009 12:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 27, 2014 3:58 AM
Points: 448, Visits: 139
Nice effort, and thanks for sharing it across.
Post #758846
Posted Friday, July 24, 2009 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 5, Visits: 88
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 :)
Post #758863
Posted Tuesday, July 28, 2009 4:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 1,184, Visits: 2,680
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
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #760653
Posted Tuesday, July 28, 2009 10:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 12:49 PM
Points: 8, Visits: 25
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

Post #761274
Posted Thursday, August 27, 2009 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 11, 2009 2:55 PM
Points: 1, Visits: 11
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...
Post #778638
Posted Thursday, August 27, 2009 11:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi,

See updated script
for this and many other modifications..in this script..

Regards.

Post #778890
Posted Monday, March 22, 2010 11:32 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:45 AM
Points: 68, Visits: 234
I only modified this to use the prefix "PEC_" rather than sp_, and my table is PEC_edi_834

The proc gets created with no error, but when I try to run the proc, I get:

Msg 156, Level 15, State 1, Procedure PEC_pec_edi_834_insert, Line 1
Incorrect syntax near the keyword 'BEGIN'.
Msg 137, Level 15, State 2, Procedure PEC_pec_edi_834_insert, Line 1
Must declare the scalar variable "@Payer_ID_Code".
Msg 137, Level 15, State 2, Procedure PEC_pec_edi_834_update, Line 1
Must declare the scalar variable "@Payer_ID_Code".
Procedure [dbo].[PEC_pec_edi_834_delete] Created Successfully

Any ideas?

Thanks.


*******************
What I lack in youth, I make up for in immaturity!
Post #887533
Posted Monday, March 22, 2010 11:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Can you post the SP which is created and giving error?
Post #887849
Posted Tuesday, March 23, 2010 6:17 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:45 AM
Points: 68, Visits: 234
It fails to create the create or insert procs. The delete proc is successfully created.



*******************
What I lack in youth, I make up for in immaturity!
Post #888021
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse