MSSQL string after the value of the partition, the table does not exist in the Insert table

The value after the string is divided, the table does not exist in the Insert table-for everyone to refer to

Use the scene, think for yourself ...

--Create a table tb1Create Tabletb1 (Colaint, Colbvarchar( -))--Inserting DataInsert  intotb1 (Cola,colb)Select 1,'A' Union  AllSelect 2,'B' Union  AllSelect 3,'C';--Stored ProceduresCreate procsp_tbtest@sid int,--ID@str varchar( -)--a,b,c,d,g asBEGINInsert  intoTB1 (Cola,colb)Select @sidSp from [dbo].[Split](@str,',')whereSp not inch(SelectColb fromTb1whereCola=@sid)ENDexecSp_tbtest4,'D,g,a,b,c';--The table is near the existence of a,b,c, the execution of the stored procedure, this time inserted is D,gSelect *  fromTb1

--Functions to achieve segmentation

ALTER function [dbo].[Split](@SourceSql varchar(8000),@Code varchar(Ten))returns @temp Table(spvarchar( +))--functions that implement the Split function--date:2007-7-10--AUTHOR:SP asbeginDeclare @i intSet @SourceSql=RTrim(LTrim(@SourceSql))Set @i=charindex(@Code,@SourceSql) while @i>=1beginInsert @temp Values( Left(@SourceSql,@i-1))Set @SourceSql=substring(@SourceSql,@i+1,Len(@SourceSql)-@i)Set @i=charindex(@Code,@SourceSql)Endif @SourceSql<>'/'Insert @temp Values(@SourceSql)returnEnd

The following is a case written by a friend Lewis; instead of a custom split function, the string is split directly in the stored procedure.

Create TableTb_test (Shopvarchar(Ten))InsertTb_testValues('a')Alter PROCEDURE [dbo]. Sp_test@strShopID varchar( +)="' asBEGINSETNOCOUNT on;Declare @tbShop Table(Shopidvarchar( +))--declare @tbTopShop table (shopid varchar (+), Num int)Set @strShopID=@strShopID+',' while(Len(@strShopID)>1)beginif  Left(@strShopID,1)=','Set @strShopID=substring(@strShopID,2,Len(@strShopID))Insert @tbShopSelect substring(@strShopID,1,charindex(',',@strShopID)-1)Set @strShopID=substring(@strShopID,charindex(',',@strShopID),Len(@strShopID))EndInserttb_testSelect *  from @tbshop whereShopid not inch(Select *  fromtb_test)ENDsp_test'a,b,c,d,e,f'Select *  fromTb_test

Blog: Http://www.haoyuncn.net/sql-split-insert

MSSQL string after the value of the partition, the table does not exist in the Insert table