sql - 如何在存储过程的 IN 过滤器内将 nvarchar 转换为 int

Question

我有一个生成文本文件的存储过程，其中有很多过滤器，就在这里

-- =============================================
-- Author:      Ricardo Ríos
-- Create date: 17/01/2014
-- Description: Genera el TXT para Importar a Saint
-- =============================================
ALTER PROCEDURE [dbo].[SP_SAINT_TXT]
    -- Add the parameters for the stored procedure here
        (   
        @nomina VARCHAR(MAX),
        @gerencia VARCHAR(MAX),
        @sucursal VARCHAR(MAX),
        @empresa VARCHAR(MAX),
        @departamento VARCHAR(MAX),
        @cargo VARCHAR(MAX),
        @horario VARCHAR(MAX),
        @locacion VARCHAR(MAX),
        @empleados VARCHAR(MAX),
        @desde DATETIME,
        @hasta DATETIME
        )
AS
BEGIN

SET NOCOUNT ON;

DECLARE @cedula varchar(max), @exnocturnas DECIMAL(5,2),
@diast DECIMAL(5,2), @diasf DECIMAL(5,0), @diasd DECIMAL(5,2),
@matut DECIMAL(5,2), @vespe DECIMAL(5,2), @noctu DECIMAL(5,2),
@linea varchar(max), @txt varchar(max),  
@l1 varchar(max),
@l2 varchar(max),
@l3 varchar(max),
@l4 varchar(max),
@l5 varchar(max),
@l6 varchar(max),
@l7 varchar(max)
SET @txt = ''

SET @nomina = (SELECT REPLACE(@nomina, '(', ''))
SET @nomina = (SELECT REPLACE(@nomina, ')', ''))
SET @gerencia = (SELECT REPLACE(@gerencia, '(', ''))
SET @gerencia = (SELECT REPLACE(@gerencia, ')', ''))
SET @sucursal = (SELECT REPLACE(@sucursal, '(', ''))
SET @sucursal = (SELECT REPLACE(@sucursal, ')', ''))
SET @empresa = (SELECT REPLACE(@empresa, '(', ''))
SET @empresa = (SELECT REPLACE(@empresa, ')', ''))
SET @departamento = (SELECT REPLACE(@departamento, '(', ''))
SET @departamento = (SELECT REPLACE(@departamento, ')', ''))
SET @cargo = (SELECT REPLACE(@cargo, '(', ''))
SET @cargo = (SELECT REPLACE(@cargo, ')', ''))
SET @locacion = (SELECT REPLACE(@locacion,'(',''))
SET @locacion = (SELECT REPLACE(@locacion,')',''))
SET @empleados = (SELECT REPLACE(@empleados,'(',''))
SET @empleados = (SELECT REPLACE(@empleados,')',''))

    declare cursor_txt cursor for
    SELECT B.ID AS cedula, 
    SUM(A.extrasnocturnas) AS extrasnocturnas, 
    SUM(A.diastrabajados) AS diastrabajados, 
    SUM(A.diasfaltantes) AS diasfaltantes, 
    SUM(A.diasdescanso) AS diasdescanso, 
    SUM(A.maturinas) AS maturinas, 
    SUM(A.vespertinas) AS vespertinas, 
    SUM(A.nocturnas) AS nocturnas
    FROM exsaint A
    RIGHT JOIN tabela B ON A.cedula = B.ID
    WHERE A.desde >= @desde AND A.hasta <= @hasta
    AND B.tipo_nomina IN (CASE WHEN @nomina = '-1' THEN B.tipo_nomina ELSE @nomina END)
    AND B.gerencia IN (CASE WHEN @gerencia = '-1' THEN B.gerencia ELSE @gerencia END)
    AND B.sucursal IN (CASE WHEN @sucursal = '-1' THEN B.sucursal ELSE @sucursal END)
    AND B.empresa IN (CASE WHEN @empresa = '-1' THEN B.empresa ELSE @empresa END)
    AND B.departamento IN (CASE WHEN @departamento = '-1' THEN B.departamento ELSE @departamento END)
    AND B.cargo IN (CASE WHEN @cargo = '-1' THEN B.cargo ELSE @cargo END)
    AND B.locacion IN (CASE WHEN @locacion = '-1' THEN B.locacion ELSE @locacion END)
    AND B.ID IN (CASE WHEN @empleados = '-1'THEN B.ID ELSE @empleados END)
    GROUP BY ID
    ORDER BY ID
    open cursor_txt
        fetch next from cursor_txt into @cedula, @exnocturnas, @diast, @diasf, @diasd, @matut, @vespe, @noctu

        while @@fetch_status = 0
        begin
            SET @linea = ''
            SET @l1 = CAST(@exnocturnas AS CHAR(8))
            SET @l2 = CAST(@diast AS CHAR(8))
            SET @l3 = CAST(@diasf AS CHAR(3))
            SET @l4 = CAST(@diasd AS CHAR(8))
            SET @l5 = CAST(@matut AS CHAR(8))
            SET @l6 = CAST(@vespe AS CHAR(8))
            SET @l7 = CAST(@noctu AS CHAR(8))
            SET @linea = 
                LTRIM(CONVERT(CHAR(16),@cedula)) +  
                LTRIM(RTRIM((SELECT CONVERT(VARCHAR(10), @hasta, 103) AS [DD/MM/YYYY]))) + 
                LTRIM(RTRIM('000')) + 
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l1)) + @l1 )) +
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l2)) + @l2 )) +
                LTRIM(RTRIM(REPLICATE('0', 3-LEN(@l3)) + @l3 )) + 
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l4)) + @l4 )) +
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l5)) + @l5 )) +
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l6)) + @l6 )) +
                LTRIM(RTRIM(REPLICATE('0', 8-LEN(@l7)) + @l7 )) +
                LTRIM(RTRIM('00000000')) +
                + CHAR(13) + CHAR(10);
            PRINT @txt
            PRINT @linea
            SET @txt = @txt + @linea
            fetch next from cursor_txt into @cedula, @exnocturnas, @diast, @diasf, @diasd, @matut, @vespe, @noctu
        end
    close cursor_txt
    deallocate cursor_txt

    SELECT @txt

END

问题是当我将一些值传递给 IN 过滤器时出现此错误。

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value ' 5 , 4 ' to data type int.

当我执行如下存储过程时。

EXECUTE SP_SAINT_TXT '-1', '-1', '-1', '( 5 , 4 )', '-1', '-1', '-1', '-1', '-1', '20140801', '20150802'   

有没有一种方法可以添加那些具有某些转换或其他功能的过滤器并且它有效？

Answer 1

IN 语句中的

CASE 似乎无法完成此任务，因为 CASE 只能返回标量值 according to this post .此外，SQL Server 不能自然地处理逗号分隔值。如果您有能力将参数更改为更合适的数据类型，如表参数，那将是最终的解决方案。

否则，您可以尝试为此任务实现一些可能的解决方法 here .这个基于 the answer by @CeejeeB .更改您的 SQL 的这一部分..:

AND B.empresa IN (CASE WHEN @empresa = '-1' THEN B.empresa ELSE @empresa END)

..对此:

AND (
     @empresa = '-1' 
        or 
     B.empresa IN (SELECT t.id.value('.', 'int') id FROM @empresaXml.nodes('//s') as t(id))
    )

当 @empresa = '-1' 时，上述语句的计算结果始终为 True。否则，int 值将从 @empresaXml 变量中提取，并将用于过滤列 empresa。

变量 @empresaXml 自行声明并填充如下:

DECLARE @empresaXml XML
SET @empresaXml = CAST('<s>' + REPLACE(@empresa, ',', '</s><s>') + '</s>' AS XML)

SQL Fiddle Demo :

CREATE TABLE MyTable(empresa int, data varchar(10))
;

INSERT INTO MyTable
VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e')
;

Declare @empresa varchar(50) = '( 5 , 4 )'
DECLARE @empresaXml XML

SET @empresa = REPLACE(REPLACE(@empresa,'(',''), ')', '')
SET @empresaXml = CAST('<s>' + REPLACE(@empresa, ',', '</s><s>') + '</s>' AS XML)

SELECT *
FROM MyTable B
WHERE (@empresa = '-1' or B.empresa IN (SELECT t.id.value('.', 'int') id
                          FROM @empresaXml.nodes('//s') as t(id)))

输出:

| empresa | data |
|---------|------|
|       4 |    d |
|       5 |    e |