create table goods(gid int identity(1,1) not null primary key,gname nvarchar(20))goalter proc splitstring@goodlist varchar(1000), ----字符串@spilststring varchar(10)=',' ----分割符asbegin declare @end int,@start int,@values char(20) ----@end 分割符位置,@start开始截取位置,@values截取的信息 set @start=1 set @end=charindex(@spilststring,@goodlist,@start) ----获得分割符的位置 while(@start
or
分解多个字符串
分隔字符串存储过程 CutStringcreate proc CutString (@sourcestring varchar(100) output, ----输入的字符串 @outstring varchar(10) output) ----输出的字符串asdeclare @position int set @position = charindex(',',@sourcestring) ----分割符的位置if (@position = 0) ----说明后面没有数据了,这是最后一个 begin set @outstring = @sourcestring set @sourcestring = null endelse begin set @outstring = substring(@sourcestring,1,@position-1) set @sourcestring = substring(@sourcestring,@position+1,(len(@sourcestring)-@position)) end ps:截取字符串‘aa,bb,cc,dd’ 第一次: 截取字符串‘aa,bb,cc,dd’ 输出 aa 第二次: 截取字符串‘bb,cc,dd’ 输出 bb 第三次: 截取字符串'cc,dd' 输出 cc 第四次: 截取字符串‘dd’ 输出 dd
使用上面的方法:
将多个字符串添加到表中
order表:
orderid(订购id) | customerid(顾客id) | employeeid(服务员id) |
Order Details表:
customerid(顾客id) | productid(编号) | unitprice(单价) | quantity(数量) | discount(折扣) |
Create proc SubmitOrder ( @customerid varchar(5), @employeeid int, @productidlist varchar(100), @unitpricelist varchar(100), @quantitylist varchar(100), @discountlist varchar(100))asset xact_abort on --开启自动事务会滚,出任何错误都自动回滚 declare @orderid int declare @productid varchar(20) declare @unitprice varchar(20) declare @quantity varchar(20) declare @discount varchar(20) begin transaction --使用事务来执行 insert into Orders(CustomerID,EmployeeID) values(@customerid,@employeeid) --select top 1 OrderID from Orders order by OrderID desc select @orderid = @@identity --@@identity取得刚刚完成插入的全局自动增长列的值 while ( @productidlist is not null ) begin exec CutString @productidlist output,@productid output exec CutString @unitpricelist output,@unitprice output exec CutString @quantitylist output,@quantity output exec CutString @discountlist output,@discount output insert into [Order Details] values( @orderid, convert(int,@productid), convert(money,@unitprice), convert(int,@quantity), convert(real,@discount)) --delete end Commit transaction