--事务实现实例
Create procedure prInsertProducts
(
 @intProductId int,
 @chvProductName varchar(30),
 @intProductCount int
)
AS
Declare @intErrorCode int
Select @intErrorCode=@@Error
Begin transaction
 if @intErrorCode=0
 begin
 --insert products
 insert products(ProductID,ProductName,ProductCount) 
 values(@intProductId,@chvProductName,@intProductCount)
 Select @intErrorCode=@@Error --每执行完一条t-sql语句马上进行检测,并把错误号保存到局部变量中
 end
 if @intErrorCode=0
 begin 
 --update products
 update products set ProductName='MicroComputer' where ProductID=5
 Select @intErrorCode=@@Error
 end
if @intErrorCode=0
 commit transaction
else
 rollback transaction

 Return @intErrorCode --最好返回错误代号给调用的存储过程或应用程序





use tempdb 
go

declare @A table(id int, name nvarchar(20),type nvarchar(20))
declare @B table(id int, fid int,value nvarchar(20))
insert into @A
select 1,'n1','t1' 
union all 
select 2,'n2','t2'
insert into @B
select 1,1,'v1'
union all
select 2,2,'v2'

select a.name,a.type,b.value 
from @A a left join @B b 
on a.id = b.fid and a.id =1
union all
select a.name,a.type,b.value 
from @A a left join @B b 
on a.id = b.fid and a.id = 2


declare my cursor for select name from sysobjects where xtype='U'
declare @ID nvarchar(50)
open my 
fetch next from my into @ID
while(@@FETCH_STATUS=0)
begin 
select top 10 * from @ID where 1=1
fetch next from my into @ID
end
close my 
deallocate my




sp_helptext Test_select





--------------------------------------------------
-------一对多时,多行转一列-----------------------
--------------------------------------------------
use tempdb 
Go

declare @Class table(ID int,ClassName nvarchar(10))
declare @Student table(ID int,StuName nvarchar(10),ClassID int)

insert into @Class 
select 1,'数学'
union all select 2,'语文'
union all select 3,'英语'

insert into @Student 
select 1,'张三',1
union all select 2,'张三',2
union all select 3,'李四',1
union all select 4,'王五',2
union all select 5,'王五',1

select ',' + S.StuName 
				from @Student S 
				where S.ClassID = 1
				for xml path('')

select * from @Class 
select * from @Student 
SELECT C.ID, C.ClassName
		,stuff((select ',' + S.StuName 
				from @Student S 
				where S.ClassID = C.ID 
				for xml path(''))
				,1,1,'') as stuName 
FROM @Class C

select STUFF(',张三,李四,王五',1,1,'')
Go