Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

April 4, 2010

Box Selection and Stored Procedures

Filed under: C#,VB.NET,Visual Studio @ 10:36 pm

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:

image

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:

image

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

image

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

image

Then paste again:

image

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.

image

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!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs