Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Optional Parameters in a Stored Proc?

Optional Parameters in a Stored Proc?

Optional Parameters in a Stored Proc?

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.

RE: Optional Parameters in a Stored Proc?

You can build dynamic sql in the stored procedure if you want. For example, you could pass the where clause as varchar parameter and then decide how to build in the sproc.

declare @mysql varchar(4000)

set @mysql = "whatever sql statement needed"


Please look up the syntax for Exec or ExecuteSQL.

Also, the question mark is used to indicate optional parameter.

mySP (1,?,?)

RE: Optional Parameters in a Stored Proc?

Wow. I had no idea you could pass SQL like that to a stored proc. I would imagine you lose the performance gain that I hear so many folks tout.  Because wouldn't this SQl need to be parsed as opposed to an un-dynamic sp?

RE: Optional Parameters in a Stored Proc?

Yes, there are always trade offs between efficiency and ease of development, user friendly, etc.. If the sproc was run 500 times a day probably not a big deal, but if run 500,000 times a day then that is another story.

RE: Optional Parameters in a Stored Proc?

SQL Can't accept optional parameters in stored procedures.
But You Can  use sp_executesql
to run a dynamic sql statment.

!!! Do not use sp_executesql often.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close