Select wheree ID like '%' ?

[Greg]

Well I've done some reading but I think I may be missing something here.

I am trying to pass an optional argument to a sproc that will select
with a "Where like" clause on an integer data type. I basically need to
select All id's or only One id from the customer table. Here's what I
have so far, but it's not working:

create proc sp_sproc
@s2date datetime,
@e2date datetime,
@sdate datetime,
@edate datetime,
@customerid int = '%',
@n char(1) = '%'
as
set nocount on
select
count(distinct c.customerid) as TCli
from customer c
where
customername not like 'zz%'
and Customerid like @customerid
and exists (
Select * from assignviewcust s
where c.customerid = s.customerid
and (s.Perf = 'PL'
or s.Perf = 'CO'
or s.Perf = 'RMVJ')
and
(s.startdate is not null
or s.enddate is not null)

and
((s.startdate <= @eDate and
s.enddate >= @eDate)
or (s.startdate <= @sDate and
s.enddate >= @sDate)
or (s.startdate >= @sDate and
s.enddate <= @eDate))
and s.skillcode like @n + '%'
)


the exec would be:
exed sp_sproc @s2date, @e2date, @sdate, @edate, (optional customerid),
(optional skillcode)

I get an error like so:

Syntax error converting the varchar value '%' to a column of data type int.

I can see how this would need to be an INT datatype and not a string,
but then how do I use a wildcard for an INT type? Maybe I am going
about this totally wrong?


I appreciate any help you may have.

Thanks,
Greg

[Anith Sen]

Re: Select wheree ID like '%' ?

For INT columns, you can do:

SELECT *
FROM tbl
WHERE col BETWEEN COALESCE( @param, 1)
AND COALESCE( @param, POWER( 2., 31 ) - 1) ;

This will behave exactly how COALESCE(@param , '%') for character columns.
For negative integers, you may have to change the lower boundary of the
BETWEEN clause to COALESCE( @param, -POWER( 2., 31 ) - 1)

--
-- Anith
( Please reply to newsgroups only )

[Greg]

Re: Select wheree ID like '%' ?

Anith Sen wrote:[color=blue]
> For INT columns, you can do:
>
> SELECT *
> FROM tbl
> WHERE col BETWEEN COALESCE( @param, 1)
> AND COALESCE( @param, POWER( 2., 31 ) - 1) ;
>
> This will behave exactly how COALESCE(@param , '%') for character columns.
> For negative integers, you may have to change the lower boundary of the
> BETWEEN clause to COALESCE( @param, -POWER( 2., 31 ) - 1)
>[/color]

Great! That worked! Thanks.

[Greg]

Re: Select wheree ID like '%' ?

Anith Sen wrote:[color=blue]
> For INT columns, you can do:
>
> SELECT *
> FROM tbl
> WHERE col BETWEEN COALESCE( @param, 1)
> AND COALESCE( @param, POWER( 2., 31 ) - 1) ;
>
> This will behave exactly how COALESCE(@param , '%') for character columns.
> For negative integers, you may have to change the lower boundary of the
> BETWEEN clause to COALESCE( @param, -POWER( 2., 31 ) - 1)
>[/color]

Great! That worked! Thanks.

[--CELKO--]

Re: Select wheree ID like '%' ?

>> I am trying to pass an optional argument to a sproc that will
select
with a "Where like" clause on an integer data type. <<

LIKE is a string predicate, so that makes no sense unless you havd
confused the representation of data with its meaning (i.e. numerals
and numbers are different).
[color=blue][color=green]
>> I basically need to select All id's or only One id from the[/color][/color]
customer table. <<

Pass a NULL, then use a COALESCE().

1)Did you really want to use "sp_" on the proc name, knowing it has
special meaning?

2) Why were there unused parameters in the proc header?

3) I understand how an ending date on durations can be NULL -- that's
the usual way that you know the event is still ongoing. But how can
you have an event with a NULL start date?

4) I think you are looking for durations that overlap. If so, you can
improve the logic a bit. Two durations do not overlap if one begins
after the other ends of if one ends before the other begins.
(@my_start > enddate) OR (@my_end < startdate)

Now negate it to get overlaps:
NOT ((@my_start > enddate) OR (@my_end < startdate)
becomes:
((@my_start <= enddate) AND (@my_end >= startdate))

So how does this work for you?

CREATE PROCEDURE Foobar
(@my_start_date DATETIME,
@my_end_date DATETIME,
@my_customer_id INTEGER,
@my_skill_code CHAR(1) = '_' )
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT (DISTINCT C.customer_id)
FROM customers AS C
WHERE customer_name NOT LIKE 'zz%'
AND customer_id = COALESCE (@my_customer_i d, customer_id);
AND EXISTS
(SELECT * FROM AssignViewCust AS S
WHERE C.customer_id = S.customer_id
AND S.perf IN ('PL ', 'CO ', 'RMVJ')
AND (S.startdate IS NOT NULL OR S.enddate IS NOT NULL)
AND @my_start_date <= enddate
AND @my_end_date >= startdate
AND S.skill_code LIKE @my_skill_code + '%';
END;

[--CELKO--]

Re: Select wheree ID like '%' ?

>> I am trying to pass an optional argument to a sproc that will
select
with a "Where like" clause on an integer data type. <<

LIKE is a string predicate, so that makes no sense unless you havd
confused the representation of data with its meaning (i.e. numerals
and numbers are different).
[color=blue][color=green]
>> I basically need to select All id's or only One id from the[/color][/color]
customer table. <<

Pass a NULL, then use a COALESCE().

1)Did you really want to use "sp_" on the proc name, knowing it has
special meaning?

2) Why were there unused parameters in the proc header?

3) I understand how an ending date on durations can be NULL -- that's
the usual way that you know the event is still ongoing. But how can
you have an event with a NULL start date?

4) I think you are looking for durations that overlap. If so, you can
improve the logic a bit. Two durations do not overlap if one begins
after the other ends of if one ends before the other begins.
(@my_start > enddate) OR (@my_end < startdate)

Now negate it to get overlaps:
NOT ((@my_start > enddate) OR (@my_end < startdate)
becomes:
((@my_start <= enddate) AND (@my_end >= startdate))

So how does this work for you?

CREATE PROCEDURE Foobar
(@my_start_date DATETIME,
@my_end_date DATETIME,
@my_customer_id INTEGER,
@my_skill_code CHAR(1) = '_' )
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT (DISTINCT C.customer_id)
FROM customers AS C
WHERE customer_name NOT LIKE 'zz%'
AND customer_id = COALESCE (@my_customer_i d, customer_id);
AND EXISTS
(SELECT * FROM AssignViewCust AS S
WHERE C.customer_id = S.customer_id
AND S.perf IN ('PL ', 'CO ', 'RMVJ')
AND (S.startdate IS NOT NULL OR S.enddate IS NOT NULL)
AND @my_start_date <= enddate
AND @my_end_date >= startdate
AND S.skill_code LIKE @my_skill_code + '%';
END;