-
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