T-SQL Stored Procedure使用條件式 IN

T-SQL Stored Procedure使用條件式 IN

How to pass string parameter with `IN` operator in stored procedure

(1)T-SQL (sp_ATS_ComparisonPIVOT) 主要預存程序
傳入參數：@model = N'A1234,B1234'

CREATE PROCEDURE [dbo].[sp_ATS_ComparisonPIVOT]
 
 @model       nvarchar(max)   
AS
 declare @ColumnGroup   nvarchar(max),
   @PivotSQL    nvarchar(max)
BEGIN
 
 SET NOCOUNT ON;
 
 select  @ColumnGroup = COALESCE(@ColumnGroup + ',' ,'' ) + QUOTENAME(model)
 FROM t_ATS_spec
 --請看下一行範例
 where model in (Select data From dbo.fn_slip_str(@model,','))
 GROUP BY QUOTENAME(model)
 select @ColumnGroup
 
 SELECT @PivotSQL = N'
 SELECT *
 FROM  (SELECT technicalspecification,name,model,text1 FROM t_ATS_spec) AS SourceTable
 PIVOT
 (max(text1)
 FOR
 model  IN (' + @ColumnGroup + N')
 ) AS PivotTable'
 exec(@PivotSQL)

END

(2)T-SQL(Function fn_slip_str)搭配字串分割程序使用

Create Function fn_slip_str( @InStr nvarchar(2000) , @s_char nvarchar(1) )
Returns @tb Table ( sno int , data nvarchar(100) )
As
Begin
    /*依據傳入字元進行字串分割，回傳Table*/
    /*
    Declare @InStr nvarchar(2000) , @s_char nvarchar(1);
    Set @InStr = '字串一,字串2,字串3,字串4';
    Set @s_char = ',';*/

    Set @InStr = @s_char + @InStr + @s_char;
    Declare @p1 Int , @p2 Int , @data nvarchar(100) , @sno int;
    Set @p1 = -1 ; Set @p2 = -1 ; Set @data = '' ; Set @sno = 0;
    While ( 0 Not In (@P1,@P2) ) Begin
  Set @p1 = CharIndex(@s_char,@InStr,@p1+1);
  Set @p2 = CharIndex(@s_char,@InStr,@p1+1);
  If ( 0 In (@p1,@p2) )
      Break;
  Set @data = SubString(@InStr,@p1+1,@p2-@p1-1);
  if ( @data <> '' ) Begin
      Set @sno = @sno +1;
      Insert Into @tb ( sno , data ) Values ( @sno , @data )
  End
    End
    Return
End

(3)執行結果：

Select * From dbo.fn_slip_str('字串一,字串2,字串3,字串4',',');

Ref WebSite
http://www.dotblogs.com.tw/rachen/archive/2008/05/23/4110.aspx