Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 13th, 2003, 07:24 AM
Authorized User
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Optional Stored Proc Parameters?

Can stored procedures accept optional parameters?

I am using an ADP in Access2k against an SQL Server 2k database.
I am trying to build a form that allows users to query a particular table in my database. What I would like is for them to be able to select from among 5 criteria to use in their search. I have the form set up and it even works by building an SQL string that starts out with a base string like:

strSQl = "Select * From MyTable Where "

And then based on the criteria they selected it will tack on more SQL like so:

strSQL= strSQL & "Criteria1 = 'FooShnicken'"

and so forth for each criteria. I then take this SQL string and pass it to a little module I found somewhere called InstantRst. InstantRst works great. You just pass it an SQL string and it builds the ADODB recordset and passes it back.

Well the problem is that InstantRst works great on my machine because I am on a Windows Integrated Security connection. My users around the office are not in the workgroup so to use this little helpful module I have to code my password right into it. Needless to say I don't want to do this.

So I went all over my code and anywhere I had InstantRst running I converted the SQL into a stored proc and called it that way using ADO. That all works fine but this is a tougher cookie because I don't want to build a stored proc for each scenario.

Can stored procedures accept optional parameters?

Or could I pass in blank parameters and then test for them using conditional statements in the stored proc?

Any suggetions would be greatly appreciated.
Old August 13th, 2003, 10:15 AM
Registered User
Join Date: Aug 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

I had to do something similar in one of my applications. The way I handled it, is I simply used the "like" function. I don't know if you are familiar with this, but it allows for partial searching. Say I'm looking for a record with the word "red" in one of the fields. I would put: fieldname like '%red%'. The % acts as a wildcard. If you put just he %, with no addition text, then there would be no limiation on that field.

So typically, in my VBA code I will look for empty parameters (combo boxes or whatever), then plug in %'s in the parameters instead of nulls or empty strings.

I hope this makes sense.


Old August 13th, 2003, 10:29 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

A common technique is to assume that a NULL parameter value means "don't care", i.e. that a NULL value for a parameter means that any value is acceptable for the associated column.

You then make use of the COALESCE function for each potentially searchable column, as, e.g.:
    FROM MyTable
    WHERE Criteria1=COALESCE(@Param1,Criteria1)
        AND Criteria2=COALESCE(@Param2,Criteria2)
        AND Criteria3=COALESCE(@Param3,Criteria3)
The idea is that the COALESCE function returns the first non-NULL value in its parameter list. Thus if, for example, @Param1 is not NULL, COALESCE returns the value of @Param1, so column 'Criteria1' is compared to the value of @Param1. If @Param1 is NULL, then the result is that the WHERE clause compares 'Criteria1' to itself, which is always true, so this is essentially a no-op, i.e. "Don't care".

Jeff Mason
Custom Apps, Inc.
Old August 13th, 2003, 11:46 AM
uit uit is offline
Authorized User
Join Date: Jul 2003
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts

Sure can, in your stored procedure declaration, add a default and then an IF to check it as a backup to the default.. See this example of inserting an address where AddrLine1 is the only required address line and the other 3 are optional.

Call it as spInsertAddress('123 Main St','','','',.....) just send NULL for the fields you have set as optional


@AddrLine1 varchar(100),
@AddrLine2 varchar(100) = NULL, -- defaults to null if not supplied
@AddrLine3 varchar(100) = NULL, -- defaults to null if not supplied
@AddrLine4 varchar(100) = NULL, -- defaults to null if not supplied
@City varchar(100),
@CountyCode varchar(3),
@StateCode varchar(2),
@PostalCode varchar(20),
@CountryCode varchar(3)



IF LEN(RTRIM(LTRIM(@AddrLine2))) = 0 SELECT @AddrLine2 = NULL
IF LEN(RTRIM(LTRIM(@AddrLine3))) = 0 SELECT @AddrLine3 = NULL
IF LEN(RTRIM(LTRIM(@AddrLine4))) = 0 SELECT @AddrLine4 = NULL
IF LEN(RTRIM(LTRIM(@CountyCode))) = 0 SELECT @CountyCode = NULL


Similar Threads
Thread Thread Starter Forum Replies Last Post
unicode parameters in stored proc CharityPays SQL Server 2000 1 November 12th, 2006 12:02 AM
optional where in stored proc david_ste SQL Server ASP 2 October 27th, 2005 07:16 AM
optional parameters in SP yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 7 July 13th, 2004 03:28 PM
problems with stored proc and output parameters zieg42 VB.NET 2002/2003 Basics 1 June 12th, 2004 07:11 AM
Passing Parameters to Stored Proc mdillard Classic ASP Professional 6 June 10th, 2003 10:35 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.