Stored Procedure Definition Search

I just re-discovered this little script in my “Magic SQL” folder, and thought I’d share it.

Below is a script to search the definition of all stored procedures in a database for a specified value – useful in circumstances when you’re trying to figure out if a particular column is used anywhere in procedure code, or something like that.

USE DatabaseName
 
DECLARE @SearchString VARCHAR(500)
SET @SearchString = 'Search'
 
SELECT DISTINCT
       Name
 
FROM sysobjects SO
     JOIN syscomments SC ON SO.id = SC.id
 
WHERE SO.[type] = 'P'
      AND SO.category = 0
      AND SC.[text] LIKE '%' + @SearchString + '%'

To use the script, change the USE statement to point at the database you’d like to search, and change the value of @SearchString to the value you’d like to search for.

The result set will provide the name of any procedures containing the value defined within @SearchString.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Working with Optional Parameters in Stored Procedures

Using optional parameters within a stored procedure is a good way to give the caller a little more control over what is being returned.

For example, if you were writing a stored procedure which was going to be used by a reporting system, and within the system, a user will have the ability to narrow down a search by choosing options from various drop down lists. Instead of writing many different stored procedures to handle each of the different drop down lists, you can do it all within one using optional parameters.

An “optional parameter” is a parameter which is set to NULL by default.

CREATE PROCEDURE dbo.GetSearchResults
 
@Franchise	VARCHAR(20) = NULL
@Make		VARCHAR(20) = NULL
@Colour		VARCHAR(20) = NULL
 
AS
 
SELECT Franchise,
       Make,
       Colour
 
FROM Vehicles
 
WHERE (@Franchise IS NULL OR Franchise = @Franchise) AND
      (@Make IS NULL OR Make = @Make) AND
      (@Colour IS NULL OR Colour = @Colour)

In the above example, if a value isn’t passed in for one of the parameters, then the default of NULL is used. This means that the first condition in the WHERE for that parameter (The “@Franchise IS NULL”, for example) is true because the variable is NULL, and execution continues on to the next line.

The above stored procedure can also be called in a few different ways, because of the added flexibility gained by using optional parameters:

1)

EXEC dbo.GetSearchResults

This method simply uses all of the default values for each parameter, and the query results aren’t filtered in the where clause at all

2)

EXEC dbo.GetSearchResults 'Volkswagen', 'Golf'

This execution method uses the values passed in and assigns them to the parameter in the same position that the parameter is declared. By that I mean, @Franchise is declared first in the procedure, so Volkswagen would be assigned to this variable as it’s passed in first. This method is a little dangerous, as if the value intended for @Colour is passed in first, no results will be returned.

3)

EXEC dbo.GetSearchResults @Franchise = 'Volkswagen', @Colour = 'Red'

This method is probably the safest of the bunch, as it explicitly defines the value to the parameter. Any parameters that aren’t defined in the EXEC use the default value set in the procedure.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)