Click here to Skip to main content
Click here to Skip to main content

SPGen - Stored Procedure Generator

, 12 Dec 2002 MIT
Rate this:
Please Sign up or sign in to vote.
A simple app which generates INSERT and UPDATE SQL Stored Procedure code

Summary

SPGen is a simple Windows application which can generate the TSQL code for an INSERT or UPDATE Microsoft SQL Stored Procedures. Point it at a table, click the generate button and the code is generated for you.

The article covers some basic SQLDMO (SQL Database Management Object) methods and provides a slim .NET wrapper class around SQLDMO to help you in using SQLDMO.

SPGen in action
1. A screenshot of SPGen in action with a generated Stored Procedure in the main text box

Introduction

Writing the basics of Stored Procedures is mind numbing at best, even for DBAs. Megan Forbes, myself and a few others got into a heated rant about Microsoft SQL Server Enterprise Manager and it's extreme lack of SP tools and management. I decided to write a very simple app which takes away the drudge of typing in all the base code for an SP. When you are faced with a table of 50 fields and the need to create a simple UPDATE or INSERT SP, declaring all those parameters can be akin to agreeing to be the designated driver for the office Christmas party, i.e. deadly boring.

Using the application

Extract the downloaded demo zip, or re-compile the project, and run the executable.

  1. SPGen starts up and lists all locally registered SQL Servers in the top left drop down list
  2. Select, or type in, the SQL Server you want to connect to
  3. Enter in the User Name and Password for the SQL Server. If there is no Password needed then just leave the Password field untouched
  4. Click the Connect button
  5. SPGen will now attempt to connect to the specified SQL Server and list all the Databases
  6. Once the Databases are listed, expand the Database you wish to work with
  7. SPGen will now list all the Tables within the expanded Database
  8. Now expand the Table you wish to generate an SP for
  9. There will be two options; UPDATE or INSERT. Click the one you want
  10. SPGen will now attempt to retrieve the columns for the Table (but not display them) and generate the specified SP type
  11. Once generated the code is placed in the text box on the right and you can cut & paste that code into Microsoft SQL Enterprise Manager, or Microsoft SQL Server Query Analyzer

That is the extent of SPGen's functionality. You can generate SPs for other Tables, without having to re-connect, or you can connect to another SQL Server and generate SPs for that.

SQLDMOHelper

SQLDMOHelper is a simple class which returns basic information about a SQL Server to the caller. Really it just wraps up the common methods I needed from SQLDMO into easy to use .NET methods which return easily usable data. To this end it only returns data and does not provide any methods to save changes to a SQL Server, yet.

Using SQLDMO in your .NET app is actually very simple. All you need to do is add a reference to the Microsoft SQLDMO Object Library COM object in your project. You can then utilise SQLDMO methods with the interopped SQLDMO namespace. All very simple thanks to .NET.

Property: public Array RegisteredServers

This property returns a one-dimensional string array containing the names of all registered SQL Servers in the local domain.

SQLDMO provides a class called ApplicationClass which you can use to gather this list, like so;

ArrayList aServers = new ArrayList();
SQLDMO.ApplicationClass acServers = new SQLDMO.ApplicationClass();

for (int iServerGroupCount = 1; 
        iServerGroupCount <= acServers.ServerGroups.Count; 
        iServerGroupCount++)
    for (int iServerCount = 1; 
            iServerCount <= acServers.ServerGroups.Item(
               iServerGroupCount).RegisteredServers.Count; 
            iServerCount++)
        aServers.Add(acServers.ServerGroups.Item
            (iServerGroupCount).RegisteredServers.Item(iServerCount).Name);

return aServers.ToArray();

Quite simply a new instance of ApplicationClass is created. Then a for loop runs through each ServerGroups returned and then in the second for loop adds each RegisteredServer name to the aServers ArrayList. aServers is then returned to the caller to be consumed.

ArrayList really makes working with un-known length arrays very easy. You can basically redimension the array on the fly and then once you are finished use the ToArray method to return a valid Array.

Property: public Array Databases

Databases is a property which returns, as the name suggest, a one-dimensional string array of all Databases in a specified SQL Server.

ArrayList aDatabases = new ArrayList();

foreach(SQLDMO.Database dbCurrent in Connection.Databases)
    aDatabases.Add(dbCurrent.Name);

return aDatabases.ToArray();

A simple foreach loop is run against the SQLDMO.Databases collection which is returned from Connection.Databases.

Connection is a property of SQLDMOHelper which provides a SQLDMO Server connection. You need to use the Connect method to set the Connection property up. Also remember to use the DisConnect method to, wait for it, disconnect the connection.

Databases then returns the string array of Database names for your app to use.

Property: public Array Tables

Looks familiar, doesn't it? It is. The Tables property returns a one-dimensional string array of all Table names in a specified Database.

ArrayList aTables = new ArrayList();
SQLDMO.Database dbCurrent = (SQLDMO.Database)Connection.Databases.Item(
    this.Database, Connection);

foreach(SQLDMO.Table tblCurrent in dbCurrent.Tables)
    aTables.Add(tblCurrent.Name);
				
return aTables.ToArray();

Property: public SQLDMO.Columns Fields

The Fields property however is a bit different. Instead of returning a one-dimensional string array it returns a SQLDMO.Columns collection which provides a full range of details on all columns (fields) within a table.

The code though is even simpler than before as we are really just returning what SQLDMO provides and not translating it at all:

SQLDMO.Database dbCurrent = (SQLDMO.Database)
    Connection.Databases.Item(this.Database, Connection);
SQLDMO.Table tblCurrent = (SQLDMO.Table)
    dbCurrent.Tables.Item(this.Table, Connection);

return tblCurrent.Columns;

Columns is a collection of SQLDMO.Column objects which contain various properties and methods for working on a field in a table. In SPGen only Name, DataType and Length are used, but there are many more.

Properties: string ServerName, UserName, Password, DataBase and Table

These four properties of SQLDMOHelper are simply strings which hold what SQL Server, user name, password, database and table respectively the methods of SQLDMOHelper should work on. For instance Databases requires just ServerName, UserName and Password to be filled in to work. To use Fields though you also need Database and Table filled in so that Fields knows what to work on.

StoredProcedure

The StoredProcedure class provides just one method at the moment, Generate. This, finally, is the heart of SPGen and provides the functionality for returning valid Stored Procedure code.

Method: public string Generate

Parameters:

  • StoredProcedureTypes sptypeGenerate
    An enum indicating the type of Stored Procedure to generate. StoredProcedureTypes.INSERT and StoredProcedureTypes.UPDATE are currently valid choices
  • SQLDMO.Columns colsFields
    The Columns collection to use in the generation of the Stored Procedure
  • string sTableName
    The name of the Table the INSERT or UPDATE will affect

The code within Generate is pretty straight forward and consists largely of a StringBuilder being used to construct the Stored Procedure. On that note I found the AppendFormat method of StrinbBuilder to be highly effective for this kind of work.

Take this code for instance: sParamDeclaration.AppendFormat(" @{0} {1}", new string[]{colCurrent.Name, colCurrent.Datatype});. Without the AppendFormat method one would have to do the following: sParamDeclaration += " @" + colCurrent.Name + " " + colCurrent.Datatype; This latter way is terrible to debug and hard to understand when there is a whole page of similar code. The format functionality of StringBuilder (and just String itself) makes for much more manageable and understandable string manipulation.

StringBuilder also is faster than using sSample += "Not in kansas, " + sName + ", anymore";, especially when performing many string appends. Thanks to Tom Archer's fantastic sample chapter on using String in .NET, I certainly learnt a lot from it.

One other slight item of interest in the Generate method is this:

if (
 colCurrent.Datatype == "binary" || 
 colCurrent.Datatype == "char" || 
 colCurrent.Datatype == "nchar" || 
 colCurrent.Datatype == "nvarchar" || 
 colCurrent.Datatype == "varbinary" || 
 colCurrent.Datatype == "varchar")
    sParamDeclaration.AppendFormat("({0})", colCurrent.Length);

Basically in TSQL you must only declare the length of a parametre if it is one of the above data types. If you for instance try @NameFirst int(4) in TSQL you will get back an error as you may not declare the length of an int data type. At present I know of no way to programatically detect which data types must and must not have length declarations, therefore I have used the cumbersome if block you see above. I was hoping that SqlDbType would provide the neccesary information, but it does not, rendering it slightly less useful.

Apart from the the method is as stated mainly a big string manipulation method which takes in the provided fields, loops through them and returns a Stored Procedure of the type specified.

As I find more areas to automate in regards to Stored Procedures I hope to add new methods and helpers to this class.

Other Titbits

There is not much more to say or explain about SPGen, it really is a simple app. However following are two basically unrelated but still interesting titbits that you may find useful.

app.config

I have finally found a use for the app.config file beyond the usual. With SPGen you can open up app.config and modify the ServerName, UserName and Password application keys. SPGen will then read them in when the app starts and pre-fill the fields for you. This way if you have an often used SQL Server you can just fire up SPGen and hit connect without having to re-type the details in each time.

Obviously you want to be careful with the Password key especially, but I put it in with full confidence nobody would let their app.config file go wandering.

Code wise it is really quite simple:

NameValueCollection settingsAppSettings = 
    (NameValueCollection)ConfigurationSettings.AppSettings;

if (settingsAppSettings["ServerName"] != null && 
    settingsAppSettings["ServerName"] != "")
{
    selServers.Text = settingsAppSettings["ServerName"];
    dmoMain.ServerName = settingsAppSettings["ServerName"];
}

First I create a NameValueCollection collection, simply to make working with the configuration settings easier (i.e. instead of having to type ConfigurationSettings.AppSettings["key"] each time.) Then the code checks if there is a specified key value (I did not want the "Select Server" message being removed when there was no value) and then it sets the input field up to the value.

Nothing fancy, but it gives SPGen a small measure of customisation and gives your fingers a rest. By the way, on release build of SPGen app.config is automatically renamed to SPGen.exe.config, that is the file you need to edit to put in your SQL Server details.

TextBox PasswordChar

The PasswordChar property of the TextBox is pretty simple. You give it a char value that you want displayed instead of the actual text, e.g. *.

However, what if you want to reset that same TextBox so that it no longer masks the input? In SPGen I needed to do this as I, maybe wrongly, did not include labels for my input fields. MSDN provides a clue, but does not go on to show you exactly how. After a bit of stumbling around I figured it out;

char chResetPassword = (char)0;
txtPassword.PasswordChar = chResetPassword;

So you create a char of 0 (zero) and then assign that to the PasswordChar property.

It is quite obvious once you figure it out, but can be annoying before that.

Possible Improvements & Bugs

  • Allow the app to generate SPs from a SELECT query instead of just a Table selection
  • Allow the app to actually insert the SP into the database, saving you from having to cut & paste

Conclusion

Stored Procedures are very powerful but can be a tedious affair when multiple parameteres are required. Used in conjunction with Llewellyn Pritchard's DBHelper app though, you will have an end-to-end solution to working with Stored Procedures in an easy and fast manner.

SQLDMO is also a useful means of discovering and exploring SQL Servers and Databases. The main problem, in a .NET environment though, is that SQLDMO must be used through COM Interop, which is not an optimum situation. Hopefully in the near future a .NET SQLDMO will be released (though if you care to shed some light on how SQLDMO works I would be happy to write my own SQLDMO.NET class.)

If you have any ideas as to how to improve the app then please speak up.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Paul Watson
Web Developer TSSG
Ireland Ireland
Paul is an internet developer living in Dublin, Ireland though home is still South Africa.
 
He believes in self-taught programming skills, standards based thinking and in the power of the common man.
 
Oh, and he loves photography. Make sure you don't get caught in the corner of a party when he has that photographic gleam in his eye. And if you were wondering about that bed-head photograph, wonder no longer...
Follow on   Twitter

Comments and Discussions

 
GeneralRe: Stored Procedure Wizard in SQL Server PinsitebuilderPaul Watson17-Feb-04 5:03 
GeneralDatabase Comparison Tool??? Pinmemberstamen12317-Feb-04 5:14 
GeneralRe: Database Comparison Tool??? PinsitebuilderPaul Watson17-Feb-04 5:23 
GeneralRe: Database Comparison Tool??? Pinmemberstamen12317-Feb-04 5:51 
GeneralRe: Database Comparison Tool??? Pinmemberbvrudney@hotmail.com20-Apr-04 11:30 
GeneralRe: Database Comparison Tool??? PinmemberDanny Crowell6-Jul-04 6:56 
GeneralHere's the benefit of this code Pinmembermirano13-Nov-04 4:55 
GeneralSP Generator PinmemberEric Lapouge13-Dec-02 19:24 
That's funny, I just have spent 6 hours yesterday to work on something similar.
 
For myself, I decided to stay inside of the SQL-Server and just build a SQL-code generator and also to have a unique SP for all the 3 kinds of action (Insert-Update-Delete).
 
Since my code is a bit long (actually 261 lines) and use some "advanced" technics (auto-order of tables, using of FSO inside of the SQL, auto-call of osql.exe, etc.) I guess it will go in a full article if somebody is interested.
 
Currently, it's done this way:
set nocount on
 
-- We verify that the tables, in which we will store the list of the objects to dynamically create, exist
if not exists (select * from dbo.sysobjects where id = object_id(N'dbo.#TableGenSP') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
	create table #TableGenSP (
		tbl_order int identity (1000, 100),
		tbl_name sysname not null,
		tbl_id int not null
	);
 
	create table #TempOrder (
		ftbl int not null,
		rtbl int not null,
		rorder int null
	);
end
else
begin
	truncate table #TableGenSP
	truncate table #TempOrder
end
 
-- Then we add the temp-procedure specific to keep user-modified statements (inside specific tags)
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.#usp_SaveUserCode') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure #usp_SaveUserCode
go
create procedure #usp_SaveUserCode (@FileID int, @mark varchar(128), @tabcount tinyint = 1)
as
	declare @start varchar(256)
	declare @end varchar(256)
	declare @tab char
	declare @temp varchar(1024)
	declare @command varchar(4000)
	declare @found tinyint
	declare @written tinyint
	DECLARE @OLEResult INT
	DECLARE cursSP CURSOR READ_ONLY FOR SELECT TMPSP_TEXT FROM #TEMPSP ORDER BY TMPSP_ID
 
	select @tab = char(9)
	select @start = '--' + @mark + ' START'
	select @end = '--' + @mark + ' END'
 
	select @found = 0
	select @written = 0
	open cursSP
	fetch next from cursSP into @temp
	while @@fetch_status = 0
	begin
		if patindex ('%' + @start + '%', @temp) <> 0
		begin
			select @found = 1
			select @temp = replace (replace (@temp , char(13), '') , char(10), '')
			EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			select @written = 1
		end
		else if patindex ('%' + @end + '%', @temp) <> 0
		begin
			select @found = 0
			select @temp = replace (replace (@temp , char(13), '') , char(10), '')
			EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			select @written = 2
		end
		else
		begin
			if @found = 1
			begin
				select @temp = replace (replace (@temp , char(13), '') , char(10), '')
				EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			end
		end
		fetch next from cursSP into @temp
	end
	close cursSP
	deallocate cursSP
 
	if @written = 0
	begin
		select @temp = replicate (@tab, @tabcount) + @start
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
	end
	if @written <> 2
	begin
		select @temp = replicate (@tab, @tabcount) + @end
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
	end
go
 
-- We insert first all the objects which are not referencing something else using a foreign key
insert into #TableGenSP (tbl_name, tbl_id) 
	select name, id
	from sysobjects 
	where xtype = 'U' and name not in ('dtproperties', 'TableGenSp', 'TempOrder') and id not in (select parent_obj from sysobjects where xtype = 'F')
 
-- We insert then all the foreign references
insert into #TempOrder (ftbl, rtbl) 
	select fkeyid, rkeyid from sysreferences 
	where fkeyid in (
		select parent_obj from sysobjects where xtype = 'F' and parent_obj in (select fkeyid from sysreferences)
		and	parent_obj not in (select tbl_id from #TableGenSP))
 
-- Now we have to update our two tables by using an update process ::
-- We are adding tables as long as their foreign dependencies can be resolved. 
-- This is possible if the datamodel is well built (ie. no circular dependencies between tables)
while exists (select * from #TempOrder where rorder is null)
begin
	update #TempOrder set rorder = tbl_order from #TableGenSP inner join #TempOrder on rtbl = tbl_id and rorder is null
	insert into #TableGenSP (tbl_name, tbl_id) 
		select name, id from sysobjects
		where id in (select ftbl from #TempOrder where ftbl not in (select distinct ftbl from #TempOrder where rorder is null))
			and id not in (select tbl_id from #TableGenSP)
end
 
declare @tempdir varchar(260)
declare @fname varchar(260)
declare @spname sysname
declare @svrname sysname
declare @dbosql sysname
declare @imp_user sysname
declare @table sysname
declare @name sysname
declare @col sysname
declare @xtype int
declare @coltype sysname
declare @length int
declare @id numeric
declare @SelKey varchar(2048)
declare @command varchar(4000)
declare @var varchar(255)
declare @cols varchar(4000)
declare @vars varchar(4000)
declare @crlf varchar(2)
declare @tab char
declare @first tinyint
DECLARE @FS INT, @OLEResult INT, @FileID INT
 
declare cursTable cursor read_only for select tbl_name, tbl_id from #TableGenSP order by tbl_order
 
select @tempdir = 'f:\temp'
select @svrname = 'arcturus'
select @dbosql = 'fineurope'
select @imp_user = 'fineurope'
select @crlf = char(13) + char(10)
select @tab = char(9)
 
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
 
open cursTable
fetch next from cursTable into @table, @id
while @@fetch_status = 0
begin
	select @spname = 'usp_' + @table
	select @fname = @tempdir + '\' + @spname + '.sql'
 
	CREATE TABLE #TEMPSP (TMPSP_ID int IDENTITY, TMPSP_TEXT varchar(1024))
	INSERT INTO #TEMPSP EXEC sp_helptext @SPNAME
	
	EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @fname, 2, 1
	IF @OLEResult <> 0 PRINT 'OpenTextFile'
 
	select @command = 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.' + @spname + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)' + @CRLF
	select @command = @command + 'drop procedure dbo.' + @spname + @crlf
	exec (@command)
 
	select @command = 'CREATE PROCEDURE dbo.' + @spname  + '(@action char'
 
	select @vars = ''
	declare cursCols cursor read_only for 
		select sc.name, sc.xtype, st.name, sc.length from syscolumns sc 
			inner join systypes st on sc.xusertype = st.xusertype 
		where id = @id and sc.xusertype not in (34, 35, 99) 
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid
 
	open cursCols
	fetch next from cursCols into @col, @xtype, @coltype, @length
	while @@fetch_status = 0
	begin
		select @col = replace (@col, ' ', '_')
		select @var = @col + ' ' + @coltype
		if @xtype in (167, 175)
		begin
			select @var = @var + '(' + convert (varchar(16), @length) + ')'
		end
		-- We must deal with tne N types differently
		else if @xtype in (231, 239)
		begin
			select @var = @var + '(' + convert (varchar(16), @length / 2) + ')'
		end
 
		select @command = @command + ', @' + @var + ' = null'
		select @vars = @vars + @tab + 'declare @old_' + @var + @crlf
		fetch next from cursCols into @col, @xtype, @coltype, @length
	end
	close cursCols
	deallocate cursCols
	select @command = @command + ')' + @crlf
	select @command = @command + 'as' + @crlf
	select @command = @command + @tab + 'set nocount on' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @vars
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	select @command = ''
	-- First We build the WHERE clause for all requests
	select @first = 1
	select @SelKey = ''
	declare cursKey cursor read_only for select name from syscolumns sc inner join
		(select sik.id, sik.colid from sysindexkeys sik inner join sysindexes six on sik.id = six.id and sik.indid = six.indid
			inner join sysobjects so on six.name = so.name and so.xtype = 'PK' and six.id = @id) t
		on sc.id = t.id and sc.colid = t.colid
	open cursKey
	fetch next from cursKey into @name
	while @@fetch_status = 0
	begin
		if @first = 0
		begin
			select @SelKey = @SelKey + ' AND '
		end
		select @first = 0
		select @SelKey = @SelKey + '[' + @name + '] = @' + replace (@name, ' ', '_')
		fetch next from cursKey into @name
	end
	close cursKey
	deallocate cursKey
 
	exec #usp_SaveUserCode @FileID, 'EnterSP', 1
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''
 
	-- First action is DELETE
	select @command = @tab + 'if @action = ''D''' + @crlf
	select @command = @command + @tab + 'begin'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'Before DELETE', 1
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''
 
	select @command = @tab + @tab + 'DELETE FROM [' + @table + '] WHERE ' + @SelKey + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'After DELETE', 2
 
	select @command = @tab + 'end'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	select @command = @tab + 'else if @action = ''U''' + @crlf
	select @command = @command + @tab + 'begin' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'Before UPDATE', 2
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''
 
	select @command = @tab + @tab + 'UPDATE [' + @table + '] SET'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	select @command = ''
	select @cols = ''
	select @first = 1
	declare cursCols cursor read_only for 
		select sc.name from syscolumns sc 
			inner join systypes st on sc.xtype = st.xtype 
		where id = @id and sc.autoval is null and sc.xtype not in (34, 35, 99)
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid
 
	open cursCols
	fetch next from cursCols into @col
	while @@fetch_status = 0
	begin
		select @command = @command + @tab + @tab + @tab
		if @first = 0
		begin
			select @command = @command + ', '
		end
		select @first = 0
		select @command = @command + '[' + @col + '] = @' + replace (@col, ' ', '_') + @crlf
		fetch next from cursCols into @col
	end
	close cursCols
	deallocate cursCols
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @tab + @tab + 'WHERE ' + @SelKey + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'After UPDATE', 2
 
	select @command = @tab + 'end'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	-- Then the INSERT
	select @command = @tab + 'else if @action = ''I''' + @crlf
	select @command = @command + @tab + 'begin'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	select @command = ''
	select @cols = ''
	select @vars = ''
	select @first = 1
	declare cursCols cursor read_only for 
		select sc.name from syscolumns sc 
			inner join systypes st on sc.xtype = st.xtype 
			where id = @id and sc.autoval is null and sc.xtype not in (34, 35, 99)
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid
 
	open cursCols
	fetch next from cursCols into @col
	while @@fetch_status = 0
	begin
		if @first = 0
		begin
			select @cols = @cols + ', '
			select @vars = @vars + ', '
		end
		select @first = 0
		select @cols = @cols + '[' + @col + ']'
		select @vars = @vars + '@' + replace (@col, ' ', '_')
		fetch next from cursCols into @col
	end
	close cursCols
	deallocate cursCols
 
	exec #usp_SaveUserCode @FileID, 'Before INSERT', 2
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''
 
	select @command = @tab + @tab + 'INSERT INTO [' + @table + ']' + @crlf
	select @command = @command + @tab + @tab + @tab + '(' + @cols + ')' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @tab + @tab + 'VALUES' + @crlf
	select @command = @command + @tab + @tab + @tab + '(' + @vars + ')' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'After INSERT', 2
 
	select @command = @tab + 'end' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	exec #usp_SaveUserCode @FileID, 'ExitSP', 1
 
	select @command = 'go' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
 
	EXECUTE @OLEResult = sp_OADestroy @FileID
 
	SELECT @command = 'osql.exe -S' + @SVRNAME + ' -U' + @IMP_USER + ' -P' + @IMP_USER + ' -d' + @DBOSQL + ' -i' + @FNAME + ' -n'
	EXEC master..xp_cmdshell @command
 
	if not exists (select * from dbo.sysobjects where id = object_id(N'dbo.' + @SPNAME) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		print 'Error creating : ' + @spname
	END
	ELSE
	BEGIN
		EXECUTE @OLEResult = sp_OAMethod @FS, 'DeleteFile', NULL, @FNAME, -1
		IF @OLEResult <> 0 PRINT 'WriteLine : DeleteFile ' + @FNAME + ' error ' + convert (varchar(32), @OleResult)
	END
	fetch next from cursTable into @table, @id
 
	DROP TABLE #TEMPSP
end
close cursTable
deallocate cursTable
 
-- Our job is over, we can clean everything
EXECUTE @OLEResult = sp_OADestroy @FS
 
drop table #TableGenSP
drop table #TempOrder
drop procedure #usp_SaveUserCode
This can be enhanced a little,by adding error-check code for example.
 
Eric Big Grin | :-D
GeneralRe: SP Generator PinsitebuilderPaul Watson14-Dec-02 2:39 
GeneralRe: SP Generator PinmemberEric Lapouge14-Dec-02 8:00 
GeneralSuggestion PinmemberThomas George13-Dec-02 12:56 
GeneralRe: Suggestion PinsitebuilderPaul Watson14-Dec-02 2:32 
QuestionIsn't this a function of query analyzer? Pinmemberjwelty10-Dec-02 15:41 
AnswerRe: Isn't this a function of query analyzer? Pinmembermikelb11-Dec-02 7:24 
GeneralRe: Isn't this a function of query analyzer? PinsitebuilderPaul Watson11-Dec-02 9:56 
AnswerRe: Isn't this a function of query analyzer? PinsitebuilderPaul Watson11-Dec-02 9:53 
GeneralSomething fishy in the Solution file Pinmemberleppie8-Dec-02 2:42 
GeneralRe: Something fishy in the Solution file PinsitebuilderPaul Watson8-Dec-02 6:04 
GeneralLLBLGen Pinmemberleppie8-Dec-02 2:07 
GeneralRe: LLBLGen PinsitebuilderPaul Watson8-Dec-02 6:00 
GeneralRe: LLBLGen PinmemberFrans Bouma8-Dec-02 9:04 
GeneralRe: LLBLGen Pinmemberleppie8-Dec-02 9:27 
GeneralRe: LLBLGen PinmemberFrans Bouma8-Dec-02 22:35 
GeneralRe: LLBLGen Pinmemberleppie9-Dec-02 7:31 
GeneralRe: LLBLGen PinsitebuilderPaul Watson11-Dec-02 9:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150123.1 | Last Updated 13 Dec 2002
Article Copyright 2002 by Paul Watson
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid