In SQL Server 2000 Enterprise Manager I used to use the "Create Stored
Procedure Wizard" all the time to create INSERT and UPDATE stored
procedures. While it was not perfect it was a very quick way of creating
simple stored procedures and only a few minor changes were required with
simple inserts and updates.

I'm fairly new to the SQL Server 2008 edition, but I can't seem to find a
quick way of creating an INSERT or UPDATE stored procedure. If I say have
10 or 20 input parameters it seems to take an age to manually find out their
Data Type, Size etc and type in manually, let alone the actual statement
itself.

Is there any easy way to create simple INSERT and UPDATE stored procedures
in SQL Server 2008 Management Studio?

Thanks,
Chris

There is a template for almost anything in SQL Server. Go to the View menu
and choose Template Explorer to see them. You can also right click on the
table and choose several options to generate a sql statement such as Insert,
update or select etc.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Chris" <cw (AT) community (DOT) nospam> wrote

Hi Andrew,

Thanks for the reply. I've seen both options but unless I am missing
something I don't see how a template could easily pick up all the input
parameters I would like for a given table. E.g. Before I used to create an
INSERT stored procedure that would create an input parameter automatically
for each column in the specified table. In SQL Server 2000 you could simply
un-tick the columns you did not want to be part of the insert.

Thanks,
Chris


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Well the template won't have the individual columns and the datatypes unless
you create one specifically for that. But if you use the basic template for
a sp you can then right clink on the table as I mentioned and choose Script
Table as Update or Insert and it will give you all the columns and their
datatypes as well. But if you do a simple Bing or Google search you should
find plenty of free scripts that will generate insert or update procs for
you.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Chris" <cw (AT) community (DOT) nospam> wrote

No there isn't anything this comprehensive out of the box. You could try
out Mladen's SSMS add-in though, it has exactly this functionality.

http://www.ssmstoolspack.com/Features.aspx#CRUD

The templates are very easy to customize.

A





On 8/4/09 9:06 AM, in article e8QypRQFKHA.4168 (AT) TK2MSFTNGP05 (DOT) phx.gbl, "Chris"
<cw (AT) community (DOT) nospam> wrote:

Thanks to both for replying.

I decided to create my own code to achieve this task. It simply allows me
to supply a table name and the insert and update stored procedure is
created. Will save a bit of time in the long run.

Chris


"Chris" <cw (AT) community (DOT) nospam> wrote

FYI, you probably could have saved even more time by googling first. Pretty
much anything you want to do someone else has been there before and there is
a ton of useful code or information out there for free. Don't get me wrong
people are glad to help here but stuff like this is usually worth at least a
quick search first on the net.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Chris" <cw (AT) community (DOT) nospam> wrote

Andrew,

I did search Google but couldn't find the answer I was after. My original
question was as to whether it was possible to achieve in SQL Management
Studio the same thing I could previously do in Enterprise Manager. I did
not find a Google answer because there clearly is not a way, but wanted to
confirm in this group. After this I did find a few 3rd party solutions but
decided to create my own routine so it fits my needs perfectly.

Chris


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

I'm not trying to be controversial, but could you provide more details about
how your code works better than existing code? Maybe you are doing
something that these 3rd party vendors could learn from to enhance their
offerings and save someone else time down the road.


On 8/5/09 11:54 AM, in article uY4GEUeFKHA.4316 (AT) TK2MSFTNGP04 (DOT) phx.gbl,
"Chris" <cw (AT) community (DOT) nospam> wrote:

Again it was just FYI and certainly didn't mean to push you to 3rd party
vendors. There are plenty of free solutions available. For instance a Bing
search on: sql server generate insert stored proc will provide
several free solutions that you can customize to suite your needs.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Chris" <cw (AT) community (DOT) nospam> wrote