vc0RsG9F

1A 1
2A 2
3A 3
4B 4
5B 5
6B 6
7	
8A 1
9A 2
10A 3
11	
12B 4 
13B 5 
14B 6
15	
16CREATE FUNCTION dbo.fxnExample (@Parameter1 NVARCHAR(1))
17RETURNS TABLE
18AS
19RETURN
20(
21    SELECT id, value
22    FROM TableName
23    WHERE id = @Parameter1
24)
25
26-- Usage Example
27SELECT * FROM dbo.fxnExample('A')   -- only data from 'A'
28SELECT * FROM dbo.fxnExample('B')   -- only data from 'B'
29	
30CREATE TABLE main_tab(suffix NVARCHAR(10) NOT NULL, val INT);
31
32INSERT INTO main_tab(suffix, val)
33VALUES ('A', 1), ('A', 2), ('A', 3),
34       ('B', 4), ('B', 5), ('B', 6),
35       ('C', 7), ('C', 8), ('C', 9);
36
37
38/* Get list of suffixes */
39SELECT suffix,
40      [row_id] = ROW_NUMBER() OVER(ORDER BY suffix)
41INTO #temp
42FROM main_tab
43GROUP BY suffix;
44
45DECLARE @name_suffix NVARCHAR(100),
46        @sql NVARCHAR(MAX),
47        @view_name NVARCHAR(MAX),
48        @index INT = 1,
49        @total INT = (SELECT COUNT(*) FROM #temp);
50
51/* I used simple while loop but you can change to CURSOR if needed */
52WHILE (@index <= @total)
53BEGIN
54
55   SELECT @name_suffix = suffix 
56   FROM #temp 
57   WHERE row_id = @index;
58
59   SELECT @sql = 
60          N'CREATE VIEW [dbo].[View@name_suffix]
61          AS
62          SELECT
63             t.suffix,
64             t.val
65          FROM [dbo].[main_tab] t
66          WHERE t.suffix = ''@name_suffix''
67          WITH CHECK OPTION'
68
69   SELECT 
70        @view_name = REPLACE('[dbo].[View@name]', '@name', @name_suffix)
71       ,@sql = REPLACE(@sql, '@name_suffix', @name_suffix)
72
73   /* Check if view exists, if not create one */
74   /* Instead of EXEC you can use EXEC [dbo].[sp_executesql]
75       and pass params explicitly */
76   IF OBJECT_ID(@view_name, 'V') IS NULL            
77       EXEC(@sql)
78
79   SET @index += 1;
80END            
81
82/* Check if you can query views */
83SELECT *
84FROM ViewA;
85
86SELECT *
87FROM ViewB;
88
89SELECT *
90FROM ViewC;