我有一个表,如下所示,Id是标识列。
Id | StudentId | DepartmentId | Comments | Date
我需要创建一个存储过程,它采用一个由逗号分隔的StudentId列表,并为逗号分隔列表中的每个StudentId在表中插入一个新行。
我尝试了以下方式,但不是正确的方式
CREATE PROC usp_UpdateStudentAttendance
@StudentIds VARCHAR(max),
@DepartmentId INT
AS
BEGIN
INSERT INTO [dbo].[StudentAttendance] ([StudentId], [DepartmentId], [Comments], [Date])
SELECT CAST(Items AS INT), @DepartmentId, 'Attended', GETDATE() FROM dbo.splitstring(@StudentIds, ',')
END
我收到一个错误"无效对象名"STRING_SPLIT">
看起来我的数据库版本不兼容,无法使用STRING_SPLIT,我们有其他选择吗?
尝试使用以下函数。
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
您可以尝试将XML用于此任务。举个例子:
DECLARE @DepartmentId INT = 123;
DECLARE @str NVARCHAR(100) = 'Student1, Student2, Student3';
DECLARE @t TABLE(xstr XML);
INSERT INTO @t VALUES (CAST('<students><student>' + REPLACE(@str, ', ', '</student><student>') + '</student></students>' AS XML))
SELECT T.r.value('.','varchar(100)') as student, @DepartmentId AS DepartmentId, 'Attended' AS Comments, GETDATE() AS [Date]
FROM @t
OUTER APPLY
xstr.nodes('/students/student') t(r)
您最好使用TableType参数
CREATE TYPE dbo.IntList AS TABLE (val int);
GO
CREATE OR ALTER PROC usp_UpdateStudentAttendance
@StudentIds dbo.IntList,
@DepartmentId int
AS
INSERT INTO [dbo].[StudentAttendance]
([StudentId], [DepartmentId], [Comments], [Date])
SELECT val, @DepartmentId, 'Attended', GETDATE()
FROM @StudentIds;
GO
我通常有一些标准的单列表类型,在任何地方都可以使用。
CREATE OR ALTER FUNCTION [dbo].[udf_EC_SplitString]
(
@ipString VARCHAR(8000),
@ipDelimiter CHAR(1)
)
RETURNS @myTable TABLE
(
STRING_VALUE VARCHAR(8000)
)
AS
BEGIN
DECLARE @lvDelimeterIndex INT
DECLARE @lvCurrentStringValue VARCHAR(8000)
SELECT @lvDelimeterIndex = 1
IF LEN(@ipString) < 1
OR @ipString IS NULL
RETURN
WHILE @lvDelimeterIndex != 0
BEGIN
-- Find Delimiter Location
SET @lvDelimeterIndex = CHARINDEX(@ipDelimiter, @ipString)
-- Check Index Value
IF @lvDelimeterIndex != 0
BEGIN
-- If Delimeter Found then Find Current STRING_VALUE
SET @lvCurrentStringValue = LEFT(@ipString, @lvDelimeterIndex - 1)
END
ELSE
BEGIN
-- If Delimeter Not Found then remaining string is current STRING_VALUE
SET @lvCurrentStringValue = @ipString
END
IF ( LEN(@lvCurrentStringValue) > 0 )
INSERT INTO @myTable VALUES ( @lvCurrentStringValue )
-- Find Remaining String
SET @ipString = RIGHT(@ipString, LEN(@ipString) - @lvDelimeterIndex)
-- Exit if Remaining String is Completed
IF LEN(@ipString) = 0
BREAK
END
RETURN
END
上面是带两个参数的函数,一个是用delimeter分隔的字符串,另一个是delimeter字符。正如我们正在讨论的逗号分隔字符串,所以下面的调用可以很好地解决任何问题。请尝试
select * from [dbo].[udf_EC_SplitString]('India,USA,Canada',',')