October 30, 2007 at 11:01 am
Jez, a paramater IS passed. If '' is passed, need all records. If 0 or any other number is passed, need the record for that number.
October 30, 2007 at 11:03 am
Ramesh, this returns all records when 0 is passed.
October 30, 2007 at 11:15 am
Why are you passing a '' to an INT parameter in the first place?
______________________________________________________________________
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 11:22 am
In first place, how could you able to pass an empty string to an int parameter? Unfortunately, i don't have the box right now to test for it.
Its would be interesting to know what the optimizer treats this kind of value? Meanwhile, could you try the below test and let us know what its giving you when an empty string is passed.
CREATE PROCEDURE Test
@employee int = NULL
AS
BEGIN
SELECT @Employee
SELECT * FROM Employees
WHERE EmployeeID = @Employee or @Employee IS NULL OR @Employee = ''
END
--Ramesh
October 30, 2007 at 11:32 am
'' is being treated as 0. That's the problem when i want one result set for 0 and a different one for ''.
October 30, 2007 at 11:40 am
David (10/30/2007)
'' is being treated as 0. That's the problem when i want one result set for 0 and a different one for ''.
Right, but what we're saying here is that you shouldn't pass a non integer value to an integer parameter.:hehe:
______________________________________________________________________
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 11:42 am
David (10/30/2007)
'' is being treated as 0. That's the problem when i want one result set for 0 and a different one for ''.
If it treats the empty string as 0, wonder why it's returning all rows?
Instead of passing an empty string, why can't pass a NULL value to the procedure?:cool:
--Ramesh
October 30, 2007 at 11:49 am
The user, who is not using Query Analyzer, is passing the value. If the user passes 'beans' then great, they will get an error message. I believe since ISMUMERIC('') = 0, the user will not get an error message if she passes ''. (Despite the reasoning, the user will not get an error message if '' is passed.) But if the user accidently passes an empty character, I want that character ignored and the user then given the default result set. The user also needs to get the record for 0 if she passes 0.
October 30, 2007 at 1:01 pm
David (10/30/2007)
Jez, a paramater IS passed. If '' is passed, need all records. If 0 or any other number is passed, need the record for that number.
You declare a parameter as INT.
You cannot assign '' to it.
'' is converted to 0 when you assigning it.
In you case '' effectively = 0.
[font="Courier New"]declare @a int
SET @a = ''
SELECT @a[/font]
_____________
Code for TallyGenerator
October 30, 2007 at 1:10 pm
David, you should take care of it in front end.
If value in text box is '' then you pass NULL in SP, if it any kind of integer value you pass that value.
_____________
Code for TallyGenerator
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply