Handeling NULL variable

Good afternoon,

I'd like to like how you guys handle a situation like the one I'm going to describe in just a moment. I'm pretty sure I'm not handeling it in the best way, so I'll just try to explain the situation and learn something from the gurus.

Let's imagin I have the following tables:

[Companies]Id [Uniqueidentifier] [PK]Alias [NVarChar]Status [Bit][Campanies_JobTitles]Id [PK]CompanyId [Uniqueidentifier]Alias [NVarChar]Status [Bit]

If I want to know the job titles of company 1 I could easely set the following stored procedure:

SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;

Now, if the user wants to list the job titles across all the companies I could pass a special value and identify that, as follows:

IF (@.CompanyIdISNOT NULL)BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;ENDELSE BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1ORDER BY JT.AliasASC;END

This works fine. I just find it a bit counter productive to copy paste the query and wrap it into an if statement. So, the bottom line is: how can I do this in a more "professional" way?

Best regards.

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

|||

farazsk11:

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

I understand what you are saying, although that check was not on the posted code for the sake of simplicity. But, that wouldn't answer the underlying question that is: how can I "reuse" the query so I don't have to repeat it thoughout the procedure? Is there a function that can solve this like ISNULL allows you to handle the values that return as null.

|||

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

|||

limno:

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

That was exactly what I was looking for. I didn't know ISNULL could be used in that context.

Thanks a lot limno :)