I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 216, comments - 2277, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book
My Blog Feed via Email
Follow MladenPrajdic on Twitter
Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff
<< Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT | Home | RFC 1925 - The Twelve Networking Truths - a must read >>

CRUD stored procedures generating SSMS Add-in

UPDATE:  This is now a part of a larger SSMS Add-In called SSMS Tools Pack

I thought I'd post something special as the last post of this year and i was thinking about what it would be.

Fellow SqlTeamer Rockmoose said he'd like to have an add-in for SSMS that would genereate basic

CRUD (Create - Insert, Read - Select, Update, Delete) stored procedures for a table.

And he'd like to have it on the context (right-mouse-click) menu of the table in Object Explorer in SSMS.

 

It sounded like a very cool little project and with the help of this excellent post about SSMS add-ins

I've decided to make one.

You can download it here.

Unzip the MSI and run it.

 

Tell me what you think and how it works for you.

If you find any bugs or have any future functionality wishes mail them to me via contact page.

 

Happy New Year everyone!

Print | posted on Thursday, December 28, 2006 11:10 PM | Filed Under [ SQL Server SSMS Tools Pack ]

Feedback

Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Great! :)
thanx for sharing.
12/29/2006 5:14 PM | Mladen
Gravatar

# Letem .NETem (2.)

Nepravidelný občasník lehce komentovaných linků ze světa vývoje v .NETu... Tentokrát shrnutí zajímavých
1/2/2007 6:57 AM | Weblog @ Rebex.cz :: Martin Vobr
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Thanks Mladen. Nice utility.

the MyGeneration software sounds interesting but unfortunately the website appears to be down, so I'll be sticking to your CRUD tool for now.

I guess one suggestion would be to have an option to choose which CRUD-type operation you would like to create. Sometimes I don't want to create all of them and I am too lazy to remove the others from the code.

Perhaps also an option to name the proc/s so that they can fit custom naming conventions. Once again I know it can be done through the generated code but I thought the more autonomous the better.

keep up the good work
1/3/2007 1:27 AM | Daren Kovacevic
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Good work.

It would be nice to have an option to generate "optimistic" concurency lockin as Visual Studio 2003/2005.

May be also option to generate (or not) SELECT statement after INSERT and/or UPDATE.

And option to generate SELECT by indexes.
1/10/2007 7:54 AM | Tomas Kouba
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

what exatly do you mean by: "generate SELECT by indexes." Tomas?
1/10/2007 1:18 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

For your select statements, give the parameter a default value of null and change your where statement to
WHERE ID = @ID OR @ID is null

That way you can use the procedure to select all record or a single one
1/24/2007 6:31 PM | Rick
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Mike Griffin's comment was accidently delted during maintainance, so i'm going to post it again. It was the first comment for this post and my comment on top is meant as a reply to this one.
here goes:

From Mike Griffin:
That's been done, with many a options and more, it's all free, MyGeneration Software
http://www.mygenerationsoftware.com
2/10/2007 1:00 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Any chance of including Try/Catch blocks around the generated code?

The Error stuff works like this:
DECLARE @Error nVarChar(4000)
BEGIN TRY
Statements...
BEGIN TRY
Statements...
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE()
RAISERROR('This Error Occurred: %s', 16, 1, @Error)
END CATCH
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE()
RAISERROR('This Error Occurred: %s', 16, 1, @Error)
END CATCH

The nested Try...Catch will raise the error to the outer Try...Catch, ERROR_MESSAGE() is a built in function that will return the Error causing the Catch. Others, like ERROR_NUMBER(), also exist. The 2nd parameter of RAISERROR is the Error level, level 10 can be used for information only, no error is actually thrown, but the message will be sent to output. Any level higher then 10 will cause the Catch to run. 16 is known as a standard, "something is wrong" level.

3/30/2007 5:57 PM | Tadd Stuart
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

It's in the works...
but sinnce i'm playing with this in my free time it'll be a couple of weeks before i put something out...
3/30/2007 6:00 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

When i try to launch Create CRUD Stored Procedures with the right click on table i retrive the following error:
Object Reference not set on istance of object.

Why?
Thx
4/18/2007 11:28 AM | WebZero
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I have no idea.
Can you mail me the stack trace? there should be one in the message box you're getting.

I'll be putting out a new version of this in a week or two, if that helps....
4/18/2007 11:32 AM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Message not display a stack trace.

I use another schema less than dbo, that can help you?
i have table like
[Schama1].[Table1]
[Schama1].[Table2]
[Schama2].[Table3]
[Schama2].[Table4]

I have tried also with AdventureWorks...
but :-(

here is the error:
http://img295.imageshack.us/img295/1961/errorgi8.jpg
4/19/2007 4:25 PM | WebZero
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

this is after the new window has been atuomatically opened or before?

as i haven't tested this with a SSMS version other than English, i don't know if this is an
issue.

do you have an english version of SSMS that you can try this on?
4/19/2007 4:29 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

before the new new window has been atuomatically ..

I use Italian version of SQL SERVER Developer edition.

thx
4/21/2007 3:31 PM | WebZero
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Hi,
nice work!

How can I use it for SSMS Expess Edition?

Thanx.
Regards.
4/27/2007 2:52 PM | win32nipuh
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

haven't tried it on SSMSE. but i don't see why it shouldn't
4/27/2007 3:31 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

just a suggestion...
why don´t you add "templates" for each operation..., in my case i use another namming convention... anyway its a good add-in.
5/9/2007 6:31 PM | ErickG
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Its Cool !!!!.

A good suggestion would be an interface to add templates .... as posted by ErickG.
It would be a wonderful option and save time
5/23/2007 4:09 PM | sreedharTR
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Hello, thanks for sharring.
I'm using the SSMS Express, but I can't find the option in the context menu.
The Add-in don't work with Express version ?

Thanks again!
5/29/2007 7:43 AM | Roni Peterson Xavier Carvalho
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

don't know... haven't tried SSMSE.
5/29/2007 11:01 AM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I tried this nice add-in in SQL Server 2005 and it gave me the following exception. It would be really nice to fix it for 2005. Are there any alternative tools?


I get the following exception:


Must declare the scalar variable "@TableName".
Must declare the scalar variable "@TableName".
Incorrect syntax near ')'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at a.a(String A_0, String A_1, String A_2)
6/7/2007 12:19 PM | Andrey
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

The generated stored procedure code is making the length of the fields twice that specified in the table. ie if my field is nvarchar(50) it gets written as nvarchar(100) in the CRUD procedures.
6/14/2007 12:23 PM | Chris Wedgwood
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

thanx andrey and chris..
both of these are fixed for the next release.
6/14/2007 1:34 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

does this tool works with ssmse and if yes how to install this add into to sql server express edition .
6/29/2007 7:49 AM | jhoojhar
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

no it doesn't work with ssmse
6/29/2007 10:24 AM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I have developed a similar tool which generates CRUD stored procedures and also generates data-access layer classes using the Subsonic Command line utility http://subsonicproject.com/. This add-in will not work with SSMS because the group of assemblies needed for extensiblity are native to SSMS and SQL 2005. Check out my article at http://developer.franklinrau.com/
9/14/2007 3:04 AM | Franklin Rau
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

thanx for the link. nice!
9/14/2007 10:24 AM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I have released the BETA version of my data-access layer generation tool. It is an add-in for SSMS 2005. You can find it here: http://developer.franklinrau.com/post/DAL-Generator-BETA-Version-Released!!.aspx
9/18/2007 8:26 PM | Franklin Rau
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Awsome!
9/18/2007 8:27 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I have developed a template driven code generator (Lattice.SPGen) to generate CRUD stored procedures and business entities supporting SQL Server, Oracle, MySQL, PostgreSQL and DB2.

http://www.latticesoft.com
11/3/2007 6:47 AM | Li Xin
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

This is the shit. Thanks!
11/8/2008 12:21 AM | Joe Zen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

This has saved me weeks of work, which was all done in one day thanks to your add-in.
Excellent project... many thanks!
Richard Slade
2/18/2009 4:18 PM | Richard Slade
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

what did you do with it? it sounds great!

for these kinds of productivity boosts there's a special button on the add-in homepage titled Donate :))
2/18/2009 4:21 PM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Generates .Net Error on SQL Management Studio 2005 startup, then does not exist in the menu when clicking on a table. Had to uninstall to get rid of the error message. Would have loved to be able to use this. The search continues....
5/9/2009 8:48 AM | Kurt
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

@Kurt
you have to install SP2 for SSMS. i've stopped supporting pre SP2 SSMS versions with SSMS Tools Pack 1.1
5/11/2009 10:45 AM | Mladen
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

thanks for this wonderful SSMS addon!
1/11/2010 7:48 AM | ermoas
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

Cool! Thanks!
6/18/2010 11:07 PM | Gonzalo.
Gravatar

# Stored Procedure Generation with very Advance Configurations

Stored Procedure Generator of the year is T4S - Stored Procedure Generator. Friends It's really very interesting. Try at least once.
6/26/2010 1:53 PM | T4S Stored Procedure Generator
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

This does not show up in SSMS 2008 after installation. Worked great in 2005
Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Analysis Services Client Tools 10.50.1600.1
Microsoft Data Access Components (MDAC) 6.0.6000.16386
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.17037
Microsoft .NET Framework 2.0.50727.3603
Operating System 6.0.6000
6/27/2010 1:43 PM | Michael Moore
Gravatar

# re: CRUD stored procedures generating SSMS Add-in

I LOVE this add-in. I install it on all my new machines. I have used MyGeneration for some things, it's very nice, but I LOVE the way this is just baked in and just works, no fluff, no "20 billion configuration options". Thank you so much!
7/13/2010 4:49 AM | will s
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Mladen Prajdić