Use Pubs
GO
--建立存儲過程
Create Procedure Get_Authors(@au_lname Varchar(100))
As
Begin
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = N' Select ColName As au_Id', @S2 = ''
Select @S1 = @S1 + ' , Max(Case au_id When N'''+ au_id + N''' Then Data Else '''' End) As [' + au_id + ']'
From authors Where CharIndex(',' + au_lname + ',' , ',' + @au_lname + ',') > 0
Group By au_id
Select @S2 = @S2 + ' Union All Select ' + Rtrim(ColID) + ' As ColID, au_id, N''' + Name + N''' As ColName, Rtrim(' + Name +') As Data From authors'
From SySColumns Where ID = OBJECT_ID('authors') And Name != 'au_id' Order By ColID
Select @S1 = @S1 + ' From ( ' + Stuff(@S2, 1, 11, '') + N') A Group By ColID, ColName Order By ColID'
EXEC(@S1)
End
GO
--測試
EXEC Get_Authors 'White,Green,Carson'
GO
--刪除測試環境
Drop Procedure Get_Authors
--結果
/*
au_Id 238-95-7766 213-46-8915 172-32-1176
au_lname Carson Green White
au_fname Cheryl Marjorie Johnson
phone 415 548-7723 415 986-7020 408 496-7223
address 589 Darwin Ln. 309 63rd St. #411 10932 Bigge Rd.
city Berkeley Oakland Menlo Park
state CA CA CA
zip 94705 94618 94025
contract 1 1 1
*/