October 30, 2007 at 9:16 am
DECLARE @ReturnStatus bit
SET @ReturnStatus = 0
SELECT @ReturnStatus = NULLIF(@ReturnStatus, '')
SELECT @ReturnStatus
October 30, 2007 at 9:25 am
Because the '' is being converted to a bit for the comparison in the NULLIF. When you convert a '' to BIT it returns 0, therefore NULLIF returns a NULL value because the values are evaluated as being the same.
SELECT convert(BIT, '')
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 30, 2007 at 10:10 am
Thanks Jason. Could you help me one step further? This is a basic example of how I might use this:
(Using Northwind)
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT @employee = NULLIF(@employee,'' )
SELECT * FROM Employees
WHERE EmployeeID = @Employee or @Employee IS NULL
In case a user accidently enters an empty character in his/her application that calls the sp, the paramater is treated as NULL and all records are returned. I now see, from your reply, that actually the paramater is treated as 0 and is being compared to a 0 in NULLIF. So if a user wanted to enter a value of 0, he/she gets all values instead of just one. (Let's assume there is an EmployeeID = 0). How can I get all records if the paramater is '' and just the single record when the parameter is 0?
October 30, 2007 at 10:15 am
Why not use ISNULL instead of NULLIF.
i.e.
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT * FROM Employees
WHERE EmployeeID = @Employee or ISNULL(@Employee, '') = ''
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 30, 2007 at 10:24 am
Perhaps I could look into that, but
1) In your example, a passed value of 0 returns all records
2) That example was simple. I would like to do some conditional statements based on the value of the passed paramater before the where clause so I would like to get "clean" values sooner than later.
October 30, 2007 at 10:31 am
David (10/30/2007)
Perhaps I could look into that, but1) In your example, a passed value of 0 returns all records
I disagree, in my example if you pass a zero, then you will get the employee with an ID of zero. If you pass an empty string or a NULL value, then you get all records.
2) That example was simple. I would like to do some conditional statements based on the value of the passed paramater before the where clause so I would like to get "clean" values sooner than later.
This all depends on what you want to do. Some things are in fact better to be done before the actual statement, but others are better in the statement. It just depends....:D
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 30, 2007 at 10:36 am
Jason, thanks for your help. I don't know what to say about Part 1 though. I'm using Northwind on a 2000 Server. I get all records when 0 is passed.
October 30, 2007 at 10:40 am
OOPS, my fault.:w00t:
You ARE right. I hadn't changed the '' to 0. :hehe:
change the code to ...
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT * FROM Employees
WHERE EmployeeID = @Employee or ISNULL(@Employee, 0) = 0
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 30, 2007 at 10:43 am
I still get all records when passing 0. 🙁
October 30, 2007 at 10:48 am
OK, I'm having a BAD DAY here... LOL
I need to slow down and read what I type .. 😀
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT * FROM Employees
WHERE EmployeeID = @Employee or ISNULL(@Employee, -1) = -1
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 30, 2007 at 10:50 am
Yes! That works for 0. But now I don't get any records when '' is passed.
October 30, 2007 at 10:51 am
Why don't use this....
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT * FROM Employees
WHERE EmployeeID = @Employee or @Employee IS NULL
--Ramesh
October 30, 2007 at 10:54 am
Ramesh, that does not return all records when '' is passed. Please see the third posting in this topic.
October 30, 2007 at 10:57 am
David,
If you want to pass an optional parameter then try this:
create procedure TEST @EmployeeId int = null
SELECT * FROM Employees
WHERE EmployeeID = isNull(@Employee, EmployeeID)
go
-- exec TEST 5 -- returns a single row
-- exec TEST null -- returns all rows
If the parameter is null (not passed) then the join is EmployeeId = EmployeeId so you get all rows.
Jez
October 30, 2007 at 10:58 am
David (10/30/2007)
Ramesh, that does not return all records when '' is passed. Please see the third posting in this topic.
My mistake,....
CREATE PROCEDURE Test
@employee int = NULL
AS
SELECT * FROM Employees
WHERE EmployeeID = @Employee or @Employee IS NULL OR @Employee = ''
--Ramesh
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply