[dba-SQLServer]Help with stored procedure

...or:

CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) 
AS 
SELECT au_lname, au_fname FROM authors 
WHERE au_lname = @lastname 
and au_fname = isnull(@firstname, au_fname)
go 
EXECUTE au_info @firstname = null, @lastname = 'Ringer' 
GO 
DROP PROCEDURE au_info

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy
Pang
Sent: Wednesday, October 15, 2003 1:53 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]Help with stored procedure


In a stored procedure report, how do I allow an optional filter for a
select 
statement/

For example:

use pubs
go
CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20)
AS SELECT au_lname, au_fname FROM authors WHERE au_lname = @lastname and
au_fname = @firstname go EXECUTE au_info @firstname = null, @lastname =
'Ringer' GO DROP PROCEDURE au_info

I want the stored procedure to return all records where last name is 
"ringer" (there should be two of them) but right now it returns nothing.

Thanks in advance,
Billy

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com