alter PROCEDURE [dbo].[xx]
@UserName VARCHAR(10),
@StrArray VARCHAR(800)
AS
DECLARE @P INT
DECLARE @C INT
DECLARE @Nums INT
DECLARE @m NVARCHAR(200)
DECLARE @x INT
SET @P=1
SET @C=1
SET @x=1
BEGIN TRANSACTION
Set NOCOUNT ON
CREATE TABLE #t_info
(
UserName VARCHAR(10),
Nums INT,
NAME NVARCHAR(200)
)
SET @C=CHARINDEX(',',@StrArray,@P+1)
SET @x=CHARINDEX(';',@StrArray,@P+1)
SET @Nums=CAST(SUBSTRING(@StrArray,@P,@C-@P) AS INT)--怎么对';'进行处理?
SET @m=SUBSTRING(@StrArray,@Nums,@x-(@p+1))
INSERT INTO #t_info(UserName,Nums,NAME) VALUES(@UserName,@Nums,@m)
SET @P=@C
WHILE (@P+1< LEN(@StrArray))
BEGIN
SET @C=CHARINDEX(',',@StrArray,@P+1)
SET @x=CHARINDEX(';',@StrArray,@P+1)
--这里想以@x作为判断条件的,但貌似不对
IF(@x> 0)
BEGIN
SET @Nums=CAST (SUBSTRING(@StrArray,@P+1,@C-@P-1) AS INT)--这里写的也有问题
SET @m=SUBSTRING(@StrArray,@P+1,@x-(@p+2))
INSERT INTO #t_info(UserName,Nums,name) VALUES(@UserName,@Nums,@m)
SET @P=@C
END
ELSE
BREAK
END
SET @Nums=CAST(SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P) AS INT)--这里写的也有问题
SET @m=SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P)
INSERT INTO #t_info(UserName,Nums,name) VALUES(@UserName,@Nums,@m)
SELECT * FROM #t_info
DROP TABLE #t_info
SET NOCOUNT OFF
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
ENDGO
EXEC xx '张三','45,数学;88,语文;'
--执行结果
--张三 45 数学
--张三 88 语文
@UserName VARCHAR(10),
@StrArray VARCHAR(800)
AS
DECLARE @P INT
DECLARE @C INT
DECLARE @Nums INT
DECLARE @m NVARCHAR(200)
DECLARE @x INT
SET @P=1
SET @C=1
SET @x=1
BEGIN TRANSACTION
Set NOCOUNT ON
CREATE TABLE #t_info
(
UserName VARCHAR(10),
Nums INT,
NAME NVARCHAR(200)
)
SET @C=CHARINDEX(',',@StrArray,@P+1)
SET @x=CHARINDEX(';',@StrArray,@P+1)
SET @Nums=CAST(SUBSTRING(@StrArray,@P,@C-@P) AS INT)--怎么对';'进行处理?
SET @m=SUBSTRING(@StrArray,@Nums,@x-(@p+1))
INSERT INTO #t_info(UserName,Nums,NAME) VALUES(@UserName,@Nums,@m)
SET @P=@C
WHILE (@P+1< LEN(@StrArray))
BEGIN
SET @C=CHARINDEX(',',@StrArray,@P+1)
SET @x=CHARINDEX(';',@StrArray,@P+1)
--这里想以@x作为判断条件的,但貌似不对
IF(@x> 0)
BEGIN
SET @Nums=CAST (SUBSTRING(@StrArray,@P+1,@C-@P-1) AS INT)--这里写的也有问题
SET @m=SUBSTRING(@StrArray,@P+1,@x-(@p+2))
INSERT INTO #t_info(UserName,Nums,name) VALUES(@UserName,@Nums,@m)
SET @P=@C
END
ELSE
BREAK
END
SET @Nums=CAST(SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P) AS INT)--这里写的也有问题
SET @m=SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P)
INSERT INTO #t_info(UserName,Nums,name) VALUES(@UserName,@Nums,@m)
SELECT * FROM #t_info
DROP TABLE #t_info
SET NOCOUNT OFF
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
ENDGO
EXEC xx '张三','45,数学;88,语文;'
--执行结果
--张三 45 数学
--张三 88 语文
你自己考慮一下,另外,發到SQL Server板塊去諮詢吧!