Stored procedure/adp question

[EManning]

I've got an A2K adp that allows the user to select one or all medical
residents for a report. I'd like to allow the user to select more than one
resident as well. Creating a stored procedure to select just one resident
or selecting all residents is simple. If this were an mdb, I'd construct a
SQL statement that would take all chosen residents and pull just those from
a table. Is there a way to do this in a sp?

 

[Larry Linson]

Why don't you construct the proper SQL and use that as the RecordSource for
the Report instead of worrying about a stored procedure? Just bear in mind
that SQL Server's "flavor" of SQL is a bit different than Access' "flavor"
of SQL (e.g., the "*" wildcard used in Access is "%" in MS SQL Server).

Larry Linson
Microsoft Access MVP

 

[EManning]

Thanks for your reply, Larry. That's what I thought I'd have to do.
However I wanted to make sure there wasn't something I was missing as far as
using a stored procedure for this.

 

[Lyle Fairfield]

I've got an A2K adp that allows the user to select one or all medical
residents for a report. I'd like to allow the user to select more than one
resident as well. Creating a stored procedure to select just one resident
or selecting all residents is simple. If this were an mdb, I'd construct a
SQL statement that would take all chosen residents and pull just those from
a table. Is there a way to do this in a sp?

IN

 

[EManning]

Well, I thought about that but how would I create the list of residents
chosen for the IN statement?

 

[Robertq]

Larry
try changing the where clause to have an or for the
selection values, be sure to put the paren's around the or
statement or it will always return all records

@wantres varchar(30) default ""
select * from myfile
where mycode = (@wantcode or @wantcode = "")

instead of
where mycode = @wantcode