Deborah's Developer MindScape

When I found out what box selection can do (as described in this earlier post), my first thought was of stored procedures. Especially those update stored procedures with all of that typing!

If you build your stored procedures in Visual Studio, you can save lots of typing time by leveraging the box selection feature in Visual Studio.

A simple update stored procedure looks something like this:

CREATE PROCEDURE dbo.CustomerUpdate
      @CustomerID     int,
      @LastName        varchar(50),
      @FirstName        varchar(50)
AS

      UPDATE    Customer
        SET
            LastName = @LastName,
            FirstName = @FirstName,
            LastUpdateDate = GETDATE()
        WHERE
            CustomerID = @CustomerID

Each field needs  to be typed at least three times: once in the parameters and two times in the SET statement. This provides many opportunities for typographical errors and takes time. But you can leverage box selection and copy/paste to limit that to typing each field once.

First, type in the parameters:

Then use the box selection to select the parameters that will be copied to the UPDATE statement. In this case, we don't select the @CustomerId because it does not get updated. Hold down the Alt key and select the parameters to copy:

Then put in the UPDATE statement and paste in the copied text:

Use box selection again, holding down the Alt key to select the empty space to the right of the fields:

Then paste again:

Then just put on the final touches: remove the first at signs (which you can also do easily with box selection) and add the equal signs.

This may seem like a lot of work, but when you have 50+ fields it can save you a lot of typing time.

Use this technique whenever you have to manually create update stored procedures in Visual Studio.

But to *really* save time, use a stored procedure generator as shown in this article.

Enjoy!