How should this query be written

ssmacwilliams
ssmacwilliams used Ask the Experts™
on
I need a query that I don't know how should be written.  I pass in an AdmissionKey. if it is null then I want that line of the sql to be omited how can this be done? I have tried IIF ,IF/THEN and CASE when. but nothing will insert/omit the portion of the SQL Statement.

SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID
HAVING AdmissionKey=:AdmissionKey
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID
HAVING @AdmissionKey is null or AdmissionKey=@AdmissionKey
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This should help you out:

SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID
have a look.



declare @CLIENTKEY int
declare @AdmissionKey int
declare @sql nvarchar(max)
 
set @clientkey=1
set @Admissionkey =null
 
set @sql=
'SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId 
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID'
 
if (@admissionkey is not null)
begin
	set @sql = @sql + ' HAVING AdmissionKey=:AdmissionKey '
end
 
print (@sql)
exec (@sql)

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
aneeshattingal: Sorry didn't work
rrjegan17: isn't what I asked for. I need for the query to omit/insert the whole criteria (ie AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)...this line would have to be omitted if the results were null).
RiteshShah: still trying to see if it will work.
Basically I need the query to be dynamic ,if possible
my query is dynamic, isn't it?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I need for the query to omit/insert the whole criteria (ie AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey).

Then you need to have a dynamic query as mentioned by RiteshShah above.

Author

Commented:
RiteshShah: Yes, I am getting and error "Must declare the scalar varaible '@CLIENTKEY'
well, I assumed that you are passing clientkey as a variable so I have declared it and you can use your own.
can you show me your full snippet of code?

Author

Commented:
I tried but what am I doing wrong:
:ClientKey and :AdmissionKey are the way this app passes in variables.

declare @CK int
declare @AdmissionKey int
declare @sql nvarchar(max)
 
set @clientkey=:ClientKey
set @Admissionkey =:AdmissionKey
 
set @sql=
'SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CK
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID'
 
if (@admissionkey is not null)
begin
      set @sql = @sql + ' HAVING AdmissionKey=:AdmissionKey '
end
 
exec (@sql)
The HAVING clause is only needed when you're selecting on an aggregate result of a Group By. Move the AdmissionKey criteria to the Where clause as follows:
WHERE CLIENTKEY=@CLIENTKEY
And ((AdmissionKey=@AdmissionKey) Or (@AdmissionKey Is Null))
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID

Effectively the criteria 'Or (@AdmissionKey Is Null)' means that the AdmissionKey is ignored in the query
 
what language are you using in your application?

Author

Commented:
Its called Tier
I am not familiar with that language but what you can do is, create stored procedure of the query I gave you, pass two parameter to that SP (clientkey and admissionkey), rest of the thing will be handled by my query inside SP.

Author

Commented:
I was hoping that I wouldn't have to do it that way. Oh well. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial