USE AdventureWorks2012
GO
CREATE
PROCEDURE
Shift_Get
@ShiftName NVARCHAR(500)
AS
BEGIN
;
WITH
CTE1
(
SELECT
CAST
((
'<i><j>'
+
REPLACE
(@ShiftName,
','
,
'</j></i><i><j>'
) +
'</j></i>'
)
XML) CSV
),CTE2
(i.query(
'./text()'
VARCHAR
(100))CSV
FROM
CROSS
APPLY CSV.nodes(
'/i/j'
As
x(i)
*
HumanResources.Shift
WHERE
Name
IN
CTE2)
END
Naomi N edited Revision 2. Comment: Formatting
SathyanarrayananS edited Original. Comment: minor edit
Hi Pituach,
Thanks for sharing very good article on splitting technique !!!
I will recommed and implement CLR SPLIT function in my future articles :)
Nice article.
* the splitting part in the input parameter @ShiftName better to do using CLR SPLIT function. this is a must function in any server i think! If you are not convinced by my recommendation then you can check this link: www.sqlperformance.com/.../split-strings
Considering this example , the input parameter can be maximum of
select len('day,night,evening') . I guess , I had given enough size for input parameter , let me know if it needs to be changed !!
Hi Naomi,
Do we have anything like consolidated lists for SSRS ,like we have for T-SQL - social.technet.microsoft.com/.../17785.sql-server-query-language-transact-sql.aspx
Also, why do you use nvarchar(500) parameter? Why not nvarchar(max)?
It may be a good idea to add See Also section to this (and other articles) and link to your other SSRS articles.