Can this be done? 2

[rabbit75]

I am sure this type of question has been answered before, but the search does not seem to be working for me.

The end user can enter values for a Beginning vendor and an Ending vendor - enter both (beginning and ending), enter one (beginning or ending) or leave both blank. Am trying to write a stored procedure passing in the beginning and ending values. Is there a way to write it to return the results so that:

if beginning = '' and ending = '', select all
if beginning <> '' and ending = '', select for vendor >= beginning
if beginning = '' and ending <> '', select for vendor <= ending
if beginning and ending <> '', select for between beginning and ending

or will I need to write multiple stored procedures?

Thank you,
rabbit75

 

[swampBoogie]

Yes,no problem doing it with one procedure

create procedure sb_sp_4 (
@beginning varchar(20),@ending varchar(20))
as
if @beginning = '' and @ending = ''
 select * from t
if @beginning <> '' and @ending = ''
  select * from t where vendor >= @beginning
if @beginning = '' and @ending <> ''
  select * from t where vendor <= @ending
if @beginning <> '' and @ending <> ''
  select * from t 
   where vendor between @beginning and @ending

 

[gprima]

This code will get you the vendors who have alphabetical names between 'Jones Supply' and 'Smith_Sales'

declare @BeginVendor varchar(30),
@EndVendor varchar(30)
set @BeginVendor = 'Jones Supply'
set @EndVendor = 'Smith_Sales'

Select Vendor_name from Vendors where Vendor_name between @BeginVendor and @EndVendor
order by Vendor_name

 

[rabbit75]

Thanks for the replies!

I was hoping not to have to use if's because the select will be long and making changes will mean changing each select in the if.

rabbit75

 

[swampBoogie]

select * from t
 where vendor >= case when @beginning = '' then 
     vendor else @beginning end
  and vendor <= case when @ending = '' then 
     vendor else @ending end

or if you send null it can be more compact

select * from t
 where vendor between coalesce(@beginning,vendor) and
coalesce(@ending,vendor)

 

[rabbit75]

Thanks! That's what I need!

rabbit75