Store proc problem

Hi,

Below is my store proc:

create procedure usp_find_case_by_date_usrcode_client

@usrcode as varchar(5) = '%',
@disch_dt_start as varchar (12) = '%',
@disch_dt_end as varchar (12) = '%',
@client_id char(2) = '%'
as

select distinct t.patient_id,p.first_name,p.last_name,convert(char (12),p.birthdate,101) Birthdate,sex,t.auth_id,t.place_of_service,convert (char(12),t.discharge_date,101) Discharge_date, i.service_id from patient_transaction t
inner join inpatient_service i on t.patient_id = i.patient_id and t.tran_id = i.tran_id
and CONVERT(VARCHAR,ISNULL(i.service_id ,0)) LIKE @usrcode and t. place_of_service = '1' and t.decision in ('1', '2') and
t.discharge_date between convert(char(12),@disch_dt_start,101) and convert(char(12),@disch_dt_end,101) and
CONVERT(VARCHAR,ISNULL(substring(t.patient_id,1,2) ,0)) LIKE @client_id
inner join patient p
on t.patient_id = p.patient_id
order by 1

I want to get all clients if I don't specify the parameter @client_id
but when I do that I get 0 records. What am I doing wrong??

Thanks in advance.

Reason could be --
Since you have other conditions in the join clause beside clientid, they may filter out all rows.

No, it's not, I know that for sure. It's something to do with the syntax...

Move likes to WHERE clause rather than JOIN clause

select distinct t.patient_id,p.first_name,p.last_name,convert(char (12),p.birthdate,101) Birthdate,sex,t.auth_id,t.place_of_service,convert (char(12),t.discharge_date,101) Discharge_date, i.service_id from patient_transaction t
inner join inpatient_service i on t.patient_id = i.patient_id and t.tran_id = i.tran_id
inner join patient p
on t.patient_id = p.patient_id
where CONVERT(VARCHAR,ISNULL(i.service_id ,0)) LIKE @usrcode and t. place_of_service = '1' and t.decision in ('1', '2') and
t.discharge_date between convert(char(12),@disch_dt_start,101) and convert(char(12),@disch_dt_end,101) and
CONVERT(VARCHAR,ISNULL(substring(t.patient_id,1,2) ,0)) LIKE @client_id
order by 1

The problem is in the client_id parameter data type, change it to char(1) or varchar(2) and you should be fine.

Thanks very much, that was a big help :)

In some cases it is permissible to utilize the following type of code structure.

CREATE PROCEDURE dbo.cismic_Sample
@pIntegerID INTEGER
AS

DECLARE
@minIntegerID INT
, @maxIntegerID INT

BEGIN

IF (@pIntegerID <> 0)
BEGIN
SELECT @minIntegerID = @pIntegerID, @maxIntegerID = @pIntegerID
END
ELSE
BEGIN
SELECT @minIntegerID = (SELECT MIN(IntegerID) FROM Industry)
SELECT @maxIntegerID = (SELECT MAX(IntegerID) FROM Industry)
END


SELECT DISTINCT
TestTable.Somefield
FROM TestTable
Where (TestTable.IntegerID >= @minIntegerID AND TestTable.IntegerID <= @maxIntegerID)
ORDER BY TestTable.Somefield

HTH,
Joseph