March 23, 2011 at 5:28 am
Hi there
I have a stored procedure that looks like this :
SELECT
J.Project Manager],
Case J.[Person Responsible] when '''' then ''Onbekend'' else J.[Person Responsible] end as Responsible,
TE.[Project No_],
J.Description,
J.Blocked AS ''Status'',
SUM(TE.Quantity) AS Aantal,
''RESOURCE'' AS Type,
''1. To be closed (LG)'' AS ''Kolom''
FROM dbo.[OPS$Time Entry] AS TE INNER JOIN
dbo.[OPS$Job] AS J ON TE.[Project No_] = J.No_
WHERE
TE.Closed = ''0''
and convert(varchar(10),TE.Date,103) <= convert(datetime,''' + convert(varchar(10),@dtUntil,103)+ ''',103)
and J.[Person Responsible] in (Select Value from dbo.Split(''' + @parRes + ''' ,'',''))
GROUP BY J.[Project Manager], J.[Person Responsible], TE.[Project No_], J.Description, J.Blocked
The split function looks like this :
ALTER FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(30)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
But when I select multiple persons in my report it will only render one person.
What am I missing ?
Kind regards
JV
March 23, 2011 at 11:35 am
There are several issue to be adressed:
1) it seems like the code snippet you posted is part of a dynamic SQL statement... (otherwise there's no reason to use two single quotes instead of one...)
2) The following WHERE clause is not SARGAable, leading to a table scan convert(varchar(10),TE.Date,103) <= convert(datetime,''' + convert(varchar(10),@dtUntil,103)+ ''',103)
Change the data type of @dtUntil to datetime holding the day without a specific time or use a character format like 'YYYYMMDD' and change the query to TE.Date <= @dtUntil.
3) The string split function you're using is not really of the category "best performing". Have a look at the last link in my signature for a better performing solution.
4) Finally, change the original query to use CROSS APPLY (as described in the aforementioned article) instead of WHERE IN(SELECT)
March 23, 2011 at 1:28 pm
thx, i will take a look into it !
still have lots to learn ...
JV
March 23, 2011 at 1:46 pm
If you have any further question or if you get stuck, please post back and I'm confident we can help you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply