July 23, 2009 at 9:01 am
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.
July 23, 2009 at 12:34 pm
Or
IF OBJECT_ID('dbo.SPName', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPName]
END
July 23, 2009 at 1:29 pm
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
July 23, 2009 at 3:09 pm
The current version of the script needs an identity column in the table.
July 23, 2009 at 3:15 pm
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.
July 23, 2009 at 9:35 pm
thanks for ur comment.. i would change it..
regards
deepthi
Deepthi Viswanathan Nair
July 24, 2009 at 12:51 am
Nice effort, and thanks for sharing it across.
July 24, 2009 at 1:30 am
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 🙂
July 28, 2009 at 4:41 am
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.
July 28, 2009 at 10:43 pm
I never think about that.. it's a nice catch.. i would incorporate this ans update the sp.. thanks for ur comment..
Deepthi Viswanathan Nair
August 27, 2009 at 1:26 pm
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...
August 27, 2009 at 11:11 pm
March 22, 2010 at 11:32 am
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!
March 22, 2010 at 11:42 pm
Can you post the SP which is created and giving error?
March 23, 2010 at 6:17 am
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!
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply