--事务实现实例
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