· 8 years ago · Jan 09, 2018, 03:02 PM
1ALTER FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
2RETURNS TABLE
3AS
4RETURN
5(
6 SELECT
7 T1.CODE AS 'CODE',
8 (SELECT COUNT(*)
9 FROM TABLE1 AS T2 WITH (NOLOCK)
10 WHERE T2.CODE NOT IN (@CODES)
11 ) AS 'COUNT'
12 FROM TABLE1 T1 WITH (NOLOCK)
13)
14
15SELECT * FROM fnTEST ('500,250,202,900,204,200,300,400,600,800')
16
17CREATE TYPE dbo.myFoo AS TABLE(id INT PRIMARY KEY);
18GO
19
20CREATE FUNCTION dbo.fnTest
21(
22 @codes dbo.myFoo READONLY
23)
24RETURNS TABLE
25AS
26RETURN
27(
28 SELECT
29 T1.CODE AS [CODE],
30 (SELECT COUNT(*)
31 FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
32 WHERE NOT EXISTS (SELECT 1 FROM @codes
33 WHERE id = T2.CODE)
34 ) AS [COUNT]
35 FROM dbo.TABLE1 T1 WITH (NOLOCK)
36);
37
38DECLARE @x dbo.myFoo;
39INSERT @x(id) VALUES(500),(250),(202);...
40SELECT * FROM dbo.fnTest(@x);
41
42CREATE FUNCTION dbo.SplitInts
43(
44 @List VARCHAR(MAX),
45 @Delimiter VARCHAR(32)
46)
47RETURNS TABLE
48AS
49 RETURN
50 (
51 SELECT Item = CONVERT(INT, Item)
52 FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
53 CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
54 FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
55 FROM sys.all_objects) AS n(Number)
56 WHERE Number <= CONVERT(INT, LEN(@List))
57 AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
58 ) AS y
59 );
60
61CREATE FUNCTION dbo.fnTest
62(
63 @codes VARCHAR(MAX)
64)
65RETURNS TABLE
66AS
67RETURN
68(
69 SELECT
70 T1.CODE AS [CODE],
71 (SELECT COUNT(*)
72 FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
73 WHERE NOT EXISTS (SELECT 1 FROM dbo.SplintInts(@codes, ',')
74 WHERE Item = T2.CODE)
75 ) AS [COUNT]
76 FROM dbo.TABLE1 T1 WITH (NOLOCK)
77);
78
79GO
80 SET ANSI_NULLS ON
81
82 SET QUOTED_IDENTIFIER ON
83 GO
84 Alter FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
85 RETURNS TABLE
86 AS
87 RETURN
88 (
89 SELECT
90 T1.CODE AS 'CODE',
91 (SELECT COUNT(*)
92 FROM TABLE1 AS T2 WITH (NOLOCK)
93 WHERE not exists (select rec_id from dbo.fncStrListToStringTable(@CODES) a where a.rec_id= t2.code)
94) AS 'COUNT'
95FROM TABLE1 T1 WITH (NOLOCK)
96 )
97
98 GO
99
100GO
101 SET QUOTED_IDENTIFIER ON
102 GO
103 /***************************************
104 Objective : Converts the given string List into table
105 Returns a table with Company ID ,Rec_ID, and Key_ID.
106 Key_ID is unique and is the order of the data in ths string list
107 Refer http://www.sommarskog.se/arrays-in-sql.html#tblnum-unlimited
108 ***********************************************/
109 Create FUNCTION [dbo].[fncStrListToStringTable] (@List text)
110 RETURNS @tbl TABLE (Rec_ID varchar (500) NOT NULL, Key_ID int identity(1,1))
111 AS
112 BEGIN
113 DECLARE @pos int,
114 @textpos int,
115 @chunklen smallint,
116 @str varchar(8000),
117 @tmpstr varchar(8000),
118 @leftover varchar(8000)
119
120If @List Is Not Null
121Begin
122 SET @textpos = 1
123 SET @leftover = ''
124 WHILE @textpos <= datalength(@list)
125 BEGIN
126 SET @chunklen = 8000 - datalength(@leftover)
127 SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
128 SET @textpos = @textpos + @chunklen
129
130 SET @pos = charindex(',', @tmpstr)
131 WHILE @pos > 0
132 BEGIN
133 SET @str = substring(@tmpstr, 1, @pos - 1)
134 INSERT @tbl VALUES(@str)
135 SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
136 SET @pos = charindex(',', @tmpstr)
137 END
138
139 SET @leftover = @tmpstr
140 END
141
142 IF ltrim(rtrim(@leftover)) <> ''
143 INSERT @tbl VALUES(@leftover)
144End
145
146Else
147 INSERT @tbl VALUES(0)
148
149RETURN
150 END