存储过程 - 使用 NOT IN where 子句执行查询 [英] stored proc - executing a query with NOT IN where clause

问题描述

我有一个存储过程

Create PROCEDURE abc      
  @sRemovePreviouslySelectedWhereClause nvarchar(max)
AS
BEGIN

SELECT * 
      FROM table 
     WHERE nId NOT IN (@sRemovePreviouslySelectedWhereClause)

END;

参数 @sRemovePreviouslySelectedWhereClause 可以有像 0,1 这样的值.但这失败并显示错误消息:

The parameter @sRemovePreviouslySelectedWhereClause can have values like 0,1 . But this fails with error message:

将 nvarchar 值0,1"转换为数据类型 int 时转换失败.

Conversion failed when converting the nvarchar value ' 0,1 ' to data type int.

除了动态查询，还有其他方法可以实现吗?

Is there any other way to achieve this other than dynamic queries?

推荐答案

首先，创建一个拆分函数，将分隔的字符串拆分成一个表格:

First, create a split function which splits your delimited string into a table:

CREATE FUNCTION [dbo].[Split]
(   
 @String varchar(max)
,@Delimiter char
)
RETURNS @Results table
(
 Ordinal int
,StringValue varchar(max)
)
as
begin

    set @String = isnull(@String,'')
    set @Delimiter = isnull(@Delimiter,'')

    declare
     @TempString varchar(max) = @String
    ,@Ordinal int = 0
    ,@CharIndex int = 0

    set @CharIndex = charindex(@Delimiter, @TempString)
    while @CharIndex != 0 begin     
        set @Ordinal += 1       
        insert @Results values
        (
         @Ordinal
        ,substring(@TempString, 0, @CharIndex)
        )       
        set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)     
        set @CharIndex = charindex(@Delimiter, @TempString)
    end

    if @TempString != '' begin
        set @Ordinal += 1 
        insert @Results values
        (
         @Ordinal
        ,@TempString
        )
    end

    return
end

然后按如下方式更改您的 where 子句:

Then change your where clause as follows:

select
 t.*
from [yourTable] t
where t.[ID] not in (select cast([StringValue] as int) from dbo.Split(@sRemovePreviouslySelectedWhereClause,','))

这篇关于存储过程 - 使用 NOT IN where 子句执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！