Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

CRUD stored procedures generating SSMS Add-in

by Mladen Prajdić 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!

Legacy Comments
Mladen
2006-12-29		 re: CRUD stored procedures generating SSMS Add-in
Great! :)
thanx for sharing.
Daren Kovacevic
2007-01-03		 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
Tomas Kouba
2007-01-10		 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.
Mladen
2007-01-10		 re: CRUD stored procedures generating SSMS Add-in
what exatly do you mean by: "generate SELECT by indexes." Tomas?
Rick
2007-01-24		 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
Mladen
2007-02-10		 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
Tadd Stuart
2007-03-30		 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.
Mladen
2007-03-30		 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...
WebZero
2007-04-18		 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
Mladen
2007-04-18		 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....
WebZero
2007-04-19		 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
Mladen
2007-04-19		 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?
WebZero
2007-04-21		 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
win32nipuh
2007-04-27		 re: CRUD stored procedures generating SSMS Add-in
Hi,
nice work!

How can I use it for SSMS Expess Edition?

Thanx.
Regards.
Mladen
2007-04-27		 re: CRUD stored procedures generating SSMS Add-in
haven't tried it on SSMSE. but i don't see why it shouldn't
ErickG
2007-05-09		 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.
sreedharTR
2007-05-23		 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
Roni Peterson Xavier Carvalho
2007-05-29		 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!
Mladen
2007-05-29		 re: CRUD stored procedures generating SSMS Add-in
don't know... haven't tried SSMSE.
Andrey
2007-06-07		 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)
Chris Wedgwood
2007-06-14		 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.
Mladen
2007-06-14		 re: CRUD stored procedures generating SSMS Add-in
thanx andrey and chris..
both of these are fixed for the next release.
jhoojhar
2007-06-29		 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 .
Mladen
2007-06-29		 re: CRUD stored procedures generating SSMS Add-in
no it doesn't work with ssmse
Franklin Rau
2007-09-14		 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/
Mladen
2007-09-14		 re: CRUD stored procedures generating SSMS Add-in
thanx for the link. nice!
Franklin Rau
2007-09-18		 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
Mladen
2007-09-18		 re: CRUD stored procedures generating SSMS Add-in
Awsome!
Li Xin
2007-11-03		 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
Joe Zen
2008-11-08		 re: CRUD stored procedures generating SSMS Add-in
This is the shit. Thanks!
Richard Slade
2009-02-18		 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
Mladen
2009-02-18		 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 :))
Kurt
2009-05-09		 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....
Mladen
2009-05-11		 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
ermoas
2010-01-11		 re: CRUD stored procedures generating SSMS Add-in
thanks for this wonderful SSMS addon!
Gonzalo.
2010-06-18		 re: CRUD stored procedures generating SSMS Add-in
Cool! Thanks!
T4S Stored Procedure Generator
2010-06-26		 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.
Michael Moore
2010-06-27		 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
will s
2010-07-13		 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!