Start and End var parameters for Select 2

[justamistere]

I'm a newbie, kind of.
I'm not sure how2 restrict a dataset between 2 var or varchar columns.

I'd like to create run parameters for a SELECT stmt to be use in MS-SQL2008 Reporting Services. This would be the Starting Customer Name and the Ending Customer Name.

Here are some tests I tried in the
MS SQL-Server Mgmt-Studio:

DECLARE @NAMES_START char(40), @NAMES_END char(40);
SET @NAMES_START = 'Boxer';
SET @NAMES_END = 'Crosby';
SELECT NAMES, ADDR1, CITY, STATE, ZIP FROM CUST
WHERE (RTRIM(NAMES) >= @NAMES_START) OR (RTRIM(NAMES) <= @NAMES_END)
ORDER BY NAMES

SELECT NAMES, ADDR1, CITY, STATE, ZIP FROM CUST
WHERE (NAMES LIKE 'Boxer%') OR (NAMES LIKE 'Crosby%')
ORDER BY NAMES

 

[gmmastros]

Can you show some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Change the OR to an AND

DECLARE @NAMES_START char(40), @NAMES_END char(40);
SET @NAMES_START = 'Boxer';
SET @NAMES_END = 'Crosby';
SELECT NAMES, ADDR1, CITY, STATE, ZIP FROM CUST
WHERE (RTRIM(NAMES) >= @NAMES_START) [b][COLOR=#ff0000]AND[/color][/b] (RTRIM(NAMES) <= @NAMES_END)
ORDER BY NAMES

 

[bborissov]

Your design is wrong.
Instead of keep customer name have a customer ID. Have a different table with all customers and then use that ID to get the range of customers.

What if you want to query a customer with name JOHN?
And what JOHN of ALL JOHNs you will get?
Also storing the name of the customer in EVERY record that customer is involved is NOT a good idea, just because one int field take 4 bites and a n/var/char fields take much more.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

It looks like his table is a customer master table. So why not store the customer name there? It doesn't appear to be a transactional table. Also, what's wrong with doing a search based on alphabet of the last name? Many companies segment their customers by name range, birthday, etc. I'm just sayin.

 

[justamistere]

Thanks RiverGuy, it worked!

I guess I couldn't "see the forest from the trees".
I did not design this and currently can't change the table structures.

 

[justamistere]

I originally didn't put the OR into the query. I was using the Query Designer from Microsoft. Then copied the sql into the Mgmt Studio. My goal is to make a parametized report using "MS Reporting Services 2008"

The columns ALL show "Filter", "Or...". Is there any way to change them to "AND"? I guess I could put the entire clause into the "Filter" column. I'm more used to writing SQL without generator tools.

 

[RiverGuy]

You don't have to use the GUI to create reports in SSRS. Typing them by hand works fine.