[dba-SQLServer] Stored procedure parameters

You can do it your way with two parameters:

CREATE PROCEDURE stpJCsSproc (@PKID INT NULL, @Opus NVARCHAR(10)) AS
IF ISNULL(PKID,0) <> 0
   SELECT * FROM BLAH WHERE PKID = @PKID
ELSE
   SELECT * FROM BLEH WHERE Opus = @Opus


or you can do something like this

CREATE PROCEDURE stpJCsSproc (@IntMode INT, @Opus NVARCHAR(20)) AS
IF @IntMode = 1 --By PKID
   SELECT * FROM BLAH WHERE PKID = CAST(@Opus AS INT)
ELSE IF @intMode = 2 --By Opus
   SELECT * FROM BLEH WHERE Opus = @Opus
--any future more can be added here without changing input parameters
--ELSE IF @intMode = 3 --By Composer
--   SELECT * FROM tblMusic WHERE Composer = @Opus


On Fri, Aug 5, 2011 at 2:36 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:

> I have a form which I want to open and display a single record in.  I am
> building a SP to pull just that one record but I have two different
> parameters that I can use to find the record, the PKID (an Integer) and a
> string "opus number".  IOW I will pass one or the other but not both.
>
> Should I build a single SP with two params and just do a test internal to
> the SP to determine which param has a value in it?  Should I build two
> different SPs?  Should I use some third strategy?
>
> --
> John W. Colby
> www.ColbyConsulting.com
> ______________________________**_________________
> dba-SQLServer mailing list
> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>
>