在表中为传递给存储过程的值的逗号分隔列表中的每个值插入新行



我有一个表,如下所示,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',',')

相关内容

  • 没有找到相关文章

最新更新