获取某个表中特定字段的所有字符串形式
1
/*
****************************************************************************
2 * Name: sp_GetString
3 * Author: Ridge Wong
4 * Create Date: 2005年11月24日 15:16:43
5 * Version: V1.0.1
6 * usage: Get some FIELDs value colletion of one TABLE by your Sperator
7 * Example:
8 * --[RUN]
9 * exec sp_GetString 'tbl_Ring_size','rs_id','rs_id,rsize',',','|'
10 * exec sp_GetString 'tbl_Ring_size','rs_id','rsize'
11 * --[RETURN]
12 * 1,mid|2,midi|3,wav|4,mmf|5,pmd|6,imy|7,amr|8,mld|9,adp|10,wma|11,mp3|12,mfm
13 * mid|midi|wav|mmf|pmd|imy|amr|mld|adp|wma|mp3|mfm
14 * ---------------------------------------------------
15 * remarks: Note Support a field's value is null.
16 **************************************************************************** */
17 ALTER PROCEDURE [ dbo ] . [ sp_GetString ] (
18 @tblName nvarchar ( 20 ), -- 表名
19 @getIdName nvarchar ( 20 ), -- 主键ID
20 @getFieldNames varchar ( 1000 ), -- 获取字段列表,不支持通配符号*。
21 @colSperator varchar ( 500 ) = ' , ' , -- 列分隔符
22 @rowSperator varchar ( 500 ) = ' | ' ) -- 行分割符
23 AS
24
25 declare @eof bit
26 declare @id int
27 declare @result varchar ( 8000 )
28 declare @oTemp varchar ( 4000 )
29 declare @sql nvarchar ( 4000 )
30
31 set @eof = 0
32 set @id = 0
33 set @result = ''
34
35 -- 判断是否是多列
36 if CHARINDEX ( ' , ' , @getFieldNames , 1 ) > 0
37 set @getFieldNames = ' Convert(nvarchar(4000), ' + Replace ( @getFieldNames , ' , ' , ' )
38 + ' + char ( 39 ) + @colSperator + char ( 39 ) + ' + Convert(nvarchar(4000), ' )
39 + ' ) '
40
41
42 while ( @eof = 0 )
43 begin
44
45 set @sql = ' select @id = IsNull((select top 1 ' + @getIdName + ' from ' + @tblName + ' where ' + @getIdName + ' >=@id),0) '
46 exec sp_executesql @sql ,
47 N ' @id int output ' ,
48 @id output
49
50 set @sql = ' select @oTemp = (select top 1 ' + @getFieldNames + ' from ' + @tblName + ' where ' + @getIdName + ' = ' + cast ( @id as varchar ( 20 )) + ' ) '
51 exec sp_executesql @sql ,
52 N ' @oTemp varchar(4000) output ' ,
53 @oTemp output
54
55 if @id = 0
56 set @eof = 1
57 else
58 begin
59 set @result = @result + @rowSperator + @oTemp
60 set @id = @id + 1
61 end
62 end
63
64 set @result = SubString ( @result , len ( @rowSperator ) + 1 , len ( @result ) - len ( @rowSperator ))
65 select @result as ResultString
66
2 * Name: sp_GetString
3 * Author: Ridge Wong
4 * Create Date: 2005年11月24日 15:16:43
5 * Version: V1.0.1
6 * usage: Get some FIELDs value colletion of one TABLE by your Sperator
7 * Example:
8 * --[RUN]
9 * exec sp_GetString 'tbl_Ring_size','rs_id','rs_id,rsize',',','|'
10 * exec sp_GetString 'tbl_Ring_size','rs_id','rsize'
11 * --[RETURN]
12 * 1,mid|2,midi|3,wav|4,mmf|5,pmd|6,imy|7,amr|8,mld|9,adp|10,wma|11,mp3|12,mfm
13 * mid|midi|wav|mmf|pmd|imy|amr|mld|adp|wma|mp3|mfm
14 * ---------------------------------------------------
15 * remarks: Note Support a field's value is null.
16 **************************************************************************** */
17 ALTER PROCEDURE [ dbo ] . [ sp_GetString ] (
18 @tblName nvarchar ( 20 ), -- 表名
19 @getIdName nvarchar ( 20 ), -- 主键ID
20 @getFieldNames varchar ( 1000 ), -- 获取字段列表,不支持通配符号*。
21 @colSperator varchar ( 500 ) = ' , ' , -- 列分隔符
22 @rowSperator varchar ( 500 ) = ' | ' ) -- 行分割符
23 AS
24
25 declare @eof bit
26 declare @id int
27 declare @result varchar ( 8000 )
28 declare @oTemp varchar ( 4000 )
29 declare @sql nvarchar ( 4000 )
30
31 set @eof = 0
32 set @id = 0
33 set @result = ''
34
35 -- 判断是否是多列
36 if CHARINDEX ( ' , ' , @getFieldNames , 1 ) > 0
37 set @getFieldNames = ' Convert(nvarchar(4000), ' + Replace ( @getFieldNames , ' , ' , ' )
38 + ' + char ( 39 ) + @colSperator + char ( 39 ) + ' + Convert(nvarchar(4000), ' )
39 + ' ) '
40
41
42 while ( @eof = 0 )
43 begin
44
45 set @sql = ' select @id = IsNull((select top 1 ' + @getIdName + ' from ' + @tblName + ' where ' + @getIdName + ' >=@id),0) '
46 exec sp_executesql @sql ,
47 N ' @id int output ' ,
48 @id output
49
50 set @sql = ' select @oTemp = (select top 1 ' + @getFieldNames + ' from ' + @tblName + ' where ' + @getIdName + ' = ' + cast ( @id as varchar ( 20 )) + ' ) '
51 exec sp_executesql @sql ,
52 N ' @oTemp varchar(4000) output ' ,
53 @oTemp output
54
55 if @id = 0
56 set @eof = 1
57 else
58 begin
59 set @result = @result + @rowSperator + @oTemp
60 set @id = @id + 1
61 end
62 end
63
64 set @result = SubString ( @result , len ( @rowSperator ) + 1 , len ( @result ) - len ( @rowSperator ))
65 select @result as ResultString
66