Auto Create St Procs and Compare

Question

Auto Create St Procs and Compare

mgalgey

Grasshopper

Points: 18
More actions
January 20, 2009 at 3:24 am

#94637

I need a way to auto create stored procedures to insert, update and select. I also need to be able to extract custom code from the previous version for inclusion in the new version. I have looked at the many 3rd party products that exist but none of them includes the comparison bit. I can compare using Sql Compare but that is going to take time and I may need to regenerate several stored procedures at any given time.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

mgalgey (1/20/2009)
I need a way to auto create stored procedures to insert, update and select. I also need to be able to extract custom code from the previous version for inclusion in the new version. I have looked at the many 3rd party products that exist but none of them includes the comparison bit. I can compare using Sql Compare but that is going to take time and I may need to regenerate several stored procedures at any given time.

You can use the following way:

Insert

create a single stored procedure with the parameters of table name, columns and their vales like

insert into @tablename (@columnslist) values(@columnsvalues)

and following the same approach in other areas will save the time.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

steveb. SSC-Forever Points: 46733 More actions · Answer 2

I use MyGeneration for all my sql code generation.

http://www.mygenerationsoftware.com/portal/default.aspx

not sure about the comparison part, can you explain a bit more what you need?

mgalgey Grasshopper Points: 18 More actions · Answer 3

Thanks Steveb,

I want to capture any custom code that is in the stored proc that is not auto generated and insert it into the new stored proc.

Eg

Old New

Create ..... Create ...

Insert Insert

Select Select

Update TableX SEt ValueX = 1

I Want to capture the Update line and insert it into the new stored proc.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

mgalgey (1/20/2009)
Thanks Steveb,
I want to capture any custom code that is in the stored proc that is not auto generated and insert it into the new stored proc.
Eg
Old New
Create ..... Create ...
Insert Insert
Select Select
Update TableX SEt ValueX = 1
I Want to capture the Update line and insert it into the new stored proc.

From that it sounds like you just want to export the existing procedures for creating in a new database? Is that right?

Generating procedures is different than migrating procedures. In a generation scenario, you have no procedures, but you have tables, so you something like CodeSmith (my preferred generation tool) to automatically build new procedures that will do the simple CRUD statements for you. In a migration scenario, you want to move existing procedures from one database to another. There are a number of methods for doing this. You can export the procedures with a DROP statement using the Management Studio GUI or you can use third party tools like DBGhost or Red Gate SQL Compare to generate differential scripts for migrating from one to another.

I'm just not clear on which of these two scenarios you're trying to do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mgalgey Grasshopper Points: 18 More actions · Answer 5

Grant,

I want to update existing stored procs in my db. I can generate the generic stuff using C# but what I want to do is extract the non generic tsql that is not necessarily related to that table, eg an update of another table, so that I can incorporate that tsql into my update stored proc. I am writing this in c#.

Thanks

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

So when you say you want to "extract non-generic sql" do you mean that you have stored procedures in your database that you need to pull out or that you have C# code in a different application that has ad hoc SQL statements that you're trying to retrieve?

If you're just trying to pull stored procedures out of the database using C#, you use the SMO, SQL Server Management Objects, to make direct calls to a database and script out the objects you're interested in.

If you're trying to figure out how to retrieve other C# code, I'm sorry, I can't help you on that one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning