无法从子句中的存储过程中提取数据
无法从子句中的存储过程中提取数据
提问于 2018-05-28 17:50:04
我正在尝试执行一个存储过程，但无法从中获取数据。下面是Store过程。
USE test
GO
CREATE PROCEDURE SELECT_IDs @idList nvarchar(1750)
AS
BEGIN TRY
SELECT DISTINCT child FROM example WHERE parent IN (@idList)
UNION
SELECT DISTINCT parent FROM example WHERE child IN (@idList)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
GO
EXEC SELECT_IDs @idList = ['100','101'];在执行上面的过程之后，它没有给我任何数据，就好像我运行的select查询一样，它给了我数据。
SELECT DISTINCT child FROM example WHERE parent IN ('100','101')在读完评论后，我尝试了下面的代码，但不起作用。
/*
Splits string into parts delimitered with specified character.
*/
CREATE FUNCTION [dbo].[SDF_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
@iPos int
if substring( @sString, 1, 1 ) = @cDelimiter
begin
set @iStart = 2
insert into @tParts
values( null )
end
else
set @iStart = 1
while 1=1
begin
set @iPos = charindex( @cDelimiter, @sString, @iStart )
if @iPos = 0
set @iPos = len( @sString )+1
if @iPos - @iStart > 0
insert into @tParts
values ( substring( @sString, @iStart, @iPos-@iStart ))
else
insert into @tParts
values( null )
set @iStart = @iPos+1
if @iStart > len( @sString )
break
end
RETURN
END并将存储过程修改为
USE test
GO
CREATE PROCEDURE SELECT_IDs @idList nVarchar(2048)
AS
BEGIN TRY
SELECT DISTINCT child FROM example WHERE parent IN ([dbo].[SDF_SplitString]
('@idList',',')) or child IN ([dbo].[SDF_SplitString]('@idList',','))
UNION
SELECT DISTINCT parent FROM example WHERE parent IN ([dbo].[SDF_SplitString]
(@idList,',')) or child IN ([dbo].[SDF_SplitString](@idList,','))
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
GO但是遇到了问题:既找不到列"dbo“，也找不到用户定义函数或聚合"dbo.SDF_SplitString"，或者名称不明确。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-28 17:57:13
如果您使用的是SQL Server 2016及更高版本，请尝试使用STRING_SPLIT
USE test GO
CREATE PROCEDURE SELECT_IDs @idList nvarchar(1750) AS BEGIN TRY
SELECT DISTINCT child
FROM example
WHERE parent IN
(SELECT value
FROM STRING_SPLIT(@idList, ','))
UNION
SELECT DISTINCT parent
FROM example
WHERE child IN
(SELECT value
FROM STRING_SPLIT(@idList, ',')) END TRY BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH
GO
EXEC SELECT_IDs @idList = '100,101';更新1.0 -拆分的集成函数
如下所述修改您的STORED PROCEDURE，您似乎错过了函数调用的SELECT子句。
USE test GO
CREATE PROCEDURE SELECT_IDs @idList nVarchar(2048) AS BEGIN TRY
SELECT DISTINCT child
FROM example
WHERE parent IN (SELECT * FROM SDF_SplitString(@idList, ','))
OR child IN (SELECT * FROM SDF_SplitString(@idList, ','))
UNION
SELECT DISTINCT parent
FROM example
WHERE parent IN (SELECT * FROM SDF_SplitString(@idList, ','))
OR child IN (SELECT * FROM SDF_SplitString(@idList, ',')) END TRY BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH GO页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50563545
复制相关文章
相似问题