April 17, 2012 at 3:23 pm
My query runs within 2 seconds using Management Studio, but when I put that query into a report in Visual Studio, the report won't run.
Here's what the query looks like:
select left(Sub, 12), P_NAME
from dbo.mydb (nolock)
where sub Like @SUB + '%'
I need to do it this way because the field sub is 14 characters long, and I need the first 12 to match the parameter, thus the ending wildcard '%'
Any ideas why this is happening and what I could do to work around it?
April 23, 2012 at 9:30 am
themangoagent (4/17/2012)
My query runs within 2 seconds using Management Studio, but when I put that query into a report in Visual Studio, the report won't run.Here's what the query looks like:
select left(Sub, 12), P_NAME
from dbo.mydb (nolock)
where sub Like @SUB + '%'
I need to do it this way because the field sub is 14 characters long, and I need the first 12 to match the parameter, thus the ending wildcard '%'
Any ideas why this is happening and what I could do to work around it?
I dont know if this makes a difference, but I always place my parameter with parathesis, for example, (@SUB).
That's just the way I do it and not sure it this resolve your issue. Thought I'd just throw it out just in case.
May 10, 2012 at 9:23 am
I've never encountered any issues with having the parameter with or without parenthesis.
The problem is that having the parameter all by itself will return the results without issue, but adding the wildcard to the end makes the query take ages (an hour plus). However, if I were to run this in SQL Server Management Studio and used an actual value plus the wildcard at the end, it works within 2 seconds.
May 10, 2012 at 6:43 pm
Mebbe just join on the LEFT 12?
select left(Sub, 12), P_NAME
from dbo.mydb (nolock)
where left(Sub, 12) = @SUB
or if the parameter is longer than 12...
where left(Sub, 12) = left(@SUB,12)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply