MY SP IS GOING NUTS!!
-
I have a simple SP that gets a substring from a varchar field and does a MAX on it +1.
Nothing special there. The join will take only the rows in the events table where the pu_id belongs to the line id passed in parameter (@intPlId)
When I call the SP with the line id in parameter the SP goes crazy and gets an error on the cast statement because it tries to convert a varchar ('lab-11') to an integer. It is ok that it crashes on the cast because it's obvious that 'lab-11' cannot be converted. The problem is that it SHOULD NOT get that record at all. That record has not a PU_ID that belongs to the line id passed in parameter.
Somehow, the join is discarded and the query is retreiving stuff outside the join ?!?!!?
Now, here the best part. It will work perfect only if I do not provide a parameter to the SP but hardcode the value of the @intPlId inside of it !!!
Can you beleive this ? Another workaround I found was to leave the parameter but remove the cast statement in the select, it would work also .........
Anybody have an idea of what is going on?
Thanks.
--THIS DOES NOT WORK
@intPlId integer
AS
DECLARE
@intMax integer,
@vcrMax varchar(20)
SELECT @intMax = ISNULL(MAX(cast(SUBSTRING(event_num,5,6) as integer))+1,1)
FROM Events e
join Prod_Units pu on e.pu_id = pu.pu_id
WHERE pu.pl_id = @intPlId and len(event_num) = 10
--THIS DOES WORK
AS
DECLARE
@intMax integer,
@vcrMax varchar(20),
@intPlId integer
SET @intPlId =32
SELECT @intMax = ISNULL(MAX(cast(SUBSTRING(event_num,5,6) as integer))+1,1)
FROM Events e
join Prod_Units pu on e.pu_id = pu.pu_id
WHERE pu.pl_id = @intPlId and len(event_num) = 10
-
How do you call the sp, what are the parameter values?
-
How are you calling the proc?
If you put a 'select @intPlId ' at the beginning of the proc, does the integer argument show up as you expect it to?
What is the pu.pl_id of the record that contains 'lab-11'?
And then again, I might be wrong ...
David Webb -
Ahh, I hate it why my post doesn't post
This looks like a classic case of parameter sniffing. I have seen it on very rare occasion, and can't explain it. There are some articles but I cannot find them right now, but its where the optimizer sees the parameter in the query and tries to guess what you want.
Try this.
AS
DECLARE
@intMax integer,
@vcrMax varchar(20),
@intPlId2 integer
SET @intPlId2 =@intPlId
SELECT @intMax = ISNULL(MAX(cast(SUBSTRING(event_num,5,6) as integer))+1,1)
FROM Events e
join Prod_Units pu on e.pu_id = pu.pu_id
WHERE pu.pl_id = @intPlId2 and len(event_num) = 10
Found one
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EFAA
or
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
-
have you tried inverting the order of operations ? :
SELECT @intMax = ISNULL(cast(MAX(SUBSTRING(event_num,5,6))as integer) +1,1)
FROM Events e
join Prod_Units pu on e.pu_id = pu.pu_id
WHERE pu.pl_id = @intPlId and len(event_num) = 10
* Noel -
Your substring() should return a 6 digit number, starting at space 5.
Is all of your event_num data in that format?
You might try to test if the data is correct using the isnumeric() function.
if isnumeric (SUBSTRING(event_num,5,6))) = 1 then....
-
Thanks for the replies guys.
I tried 2 of your suggestions and both work.
Ray M and Noeld are the winners.
It still doesn't explain why it behaves like this though.....
The optimizer is probably the culprit.
-
The problem with parameter sniffing is not the optimizer... it's the cached plan that assumes to much from your first call
. -
In SQL you tell the engine what you want and it determines (sometimes not so good) how to do it. To be honest this does not happens very often but it does happens
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply