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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy