search

[cyberdaemon]

i have here my search code. i want to search for firstname, and gender, but what if firstname is empty and gender is the one were going to search. but the gender is not filtering they just filter the name.

any suggestions..

    SELECT  --ACCOUNT_CODE
            --HOSPITAL_CODE
            MRN
            ,CONVERT(DATE,BIRTHDATE)BIRTHDATE
            ,FIRSTNAME 
            ,MIDDLENAME
            ,LASTNAME 
            ,GENDER
            ,MARITAL_STATUS
            ,RELIGION
            ,OCCUPATION 
            ,ADDRESS1
    FROM [dbHIS].[dbo].[PATIENT_INFORMATION]


    WHERE   (FIRSTNAME LIKE @FIRSTNAME)  or (GENDER = @GENDER) 

[ryantroop]

my SQL is.. getting better, so take this as a "starting point" and maybe someone can fix it if I did it wrong..

It looks like youre using variables as it is, so why not use SQL as a language and do your logic as necessary... (Im summarizing your code, so feel free to add the columns as necessary).

declare @FIRSTNAME varchar(255), @GENDER char(1)

SELECT * FROM PATIENT_INFO WHERE
 @FIRSTNAME = CASE
 WHEN (@FIRSTNAME is not NULL)
 THEN FIRSTNAME LIKE @FIRSTNAME order by @FIRSTNAME
 ELSE
 order by @GENDER

You will forgive me if I am totally wrong.. I learned SQL on MySQL, and I am currently forcing MSSQL/Prepared Statements into my brain.

[adam_k]

@ryantroop:
I think you are trying to do this:

declare @query varchar(500)
declare @criteria varchar(500) 

if @firstname is null and @gender is null 
return
else 
if @firstname is not null and @gender is not null 
set @criteria = ' where firstname like '%' + @firstname + '%' and gender = @gender
else 
if @firstname is null 
set @criteria = ' where gender = @gender' 
else 
set @criteria = ' where firstname like '%' + @firstname + '%'

set @query = 'SELECT MRN
,CONVERT(DATE,BIRTHDATE)BIRTHDATE
,FIRSTNAME 
,MIDDLENAME
,LASTNAME 
,GENDER
,MARITAL_STATUS
,RELIGION
,OCCUPATION 
,ADDRESS1
FROM [dbHIS].[dbo].[PATIENT_INFORMATION] '

exec(@query + @criteria) 

PS: I haven't tested the code, so it might contain errors or typos.

[ryantroop]

I see what you did, you made temporary variables to hold the queries.. neat.

However, I was under the impression that you could put the logic directly into the query, and not have to run it as such.

[adam_k]

I've never seen that. Without vars you can only use case:

Select column1, case when column2 = "ABC" then 1 when column2 = "BCD" then 2 else 3 end as columnx from table1 ....

[cyberdaemon]

thanks for the responce. however i laready solve this problem. i have logic error because i was not able to fetch the data that i want to filter in the button..