Wednesday, February 27, 2008

TRUE or FALSE

I know that sometimes you run into a problem of passing a parameter into a stored procedure that indicates TRUE or FALSE. But you need to return both fields true and false on say true and only false on false. There are a number of ways to solve this problem but one solution stands out as the winner for me.

So assuming our table Documents has a fields called Name (varchar) and Private (bit), we want to return all records from the table when we pass in TRUE and only the Non Private records (FALSE) when we pass in false.

Input Parameter for the stored procedure is called @Private BIT,

SELECT Name
FROM Documents As [d]
WHERE [d].[Private] < CASE WHEN @Private = 1 THEN 2 ELSE 1 END
So now our select will return both TRUE and FALSE when then @Private parameter is true and only false when it is false.
I think this is a helpful tidbit of info to help you out on your next stored procedure.
I credit Jon Marshall for this code.