Optional Parameters in a Stored Proc?

[VBAHole22]

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.

 

[cmmrfrds]

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.

Example.
declare @mysql varchar(4000)

set @mysql = "whatever sql statement needed"

Exec(@mysql)
or
ExecuteSQL(@mysql)

Please look up the syntax for Exec or ExecuteSQL.

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

mySP (1,?,?)

 

[VBAHole22]

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?

 

[cmmrfrds]

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.

 

[yaya013]

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.