In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.
The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.
The MemberId field is a varchar(20) in the table.
Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @.memberid
EXEC sp_GetClaims_BY_MemberID '99999999999'
The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?
I am currently working in SQL 2000.
Here's one way I think should work.
Assume we have decided that '*' (star) is to mean 'all' (as in the T-SQL wildcard)
What below does, is just to turn '*' into null, and in the WHERE, if the var is null, use the column instead.
End result for parameter '*', is then WHERE Membid = Membid, which is what you want - all rows.
Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = COALESCE(NULLIF( @.memberid, '*'), Membid)
=;o)
/Kenneth
