sql中执行一个sp的问题 [英] Problems with the execution of a sp in sql

问题描述

我一直在尝试执行下一个 sp.我的目标是生成一个包含多个 ID 值的字符串.这就是我所做的:

I've been trying to execute the next sp. My target is to generate a string with the values of several ID. This is what I've done:

ALTER PROCEDURE SP_IDENTIF @ID NVARCHAR OUTPUT
AS
BEGIN       
        DECLARE 
        @DATUM NVARCHAR(MAX),
        @CONCAT NVARCHAR(MAX), 
        @IDENTIFIC NVARCHAR(MAX),
        @T DATETIME ;
        SET @IDENTIFIC = '';

        DECLARE IDENTIFIERS CURSOR LOCAL FOR 
            SELECT TOP 2 ID
            FROM TABLE_1
            WHERE ID IS NOT NULL
            UNION 
            SELECT TOP 2 ID
            FROM TABLE_2
            WHERE ID IS NOT NULL

        OPEN IDENTIFIERS
            FETCH NEXT FROM IDENTIFIERS INTO @DATUM
                WHILE @@FETCH_STATUS = 0
                    BEGIN  
                    PRINT @IDENTIFIC;
                    SELECT @IDENTIFIC = CONCAT(@IDENTIFIC,',OR ID =''',@DATUM,'''');
                    FETCH NEXT FROM IDENTIFIERS INTO @DATOS;    
                    END;

        CLOSE IDENTIFIERS;
        SET @ID =  @IDENTIFIC;  
        PRINT('THE IDENTIFIERS ARE: '+@ID);
        RETURN;
END

如果我执行联合查询，我将得到下一个输出(显然 top 关键字只是为了测试结果):

If I execute the union query I will have the next output (obviously the top keyword is just to test the result):

ID
101075330-IC001
ACP-2582785
ACP-645655
ACP-942612

但是当我调用 sp 时，我得到了下一个输出:

But when I call the sp I am getting the next output:

declare @identific nvarchar(max);
exec sp_Identif @identific output;
print 'The ID set is: '+@identific;


,OR ID ='101075330-IC001'
,OR ID ='101075330-IC001',OR ID ='ACP-2582785'
,OR ID ='101075330-IC001',OR ID ='ACP-2582785',OR ID ='ACP-645655'
THE IDENTIFIERS ARE : ,
The ID set is : 

我正在打印变量@identif 和@Id 的结果，以检查其值发生了什么.正如您所看到的，在 fetch 循环期间，字符串变量中仅插入了 3 个 Id，但在循环结束时，我在输出变量中没有任何值 recordint.

I am printing the result of the variable @identif and @Id to check what is going on with its value. As you can see can see only 3 Id's inserted in the string variable durint the fetch loop, but at the end of the cycle I do not have any value recordint in the output variable.

这段代码我做错了什么?还有其他方法可以达到相同的目标吗?.我正在尝试实现一个将从 SSIS 调用的 SP.

What am I doing wrong with this code?, and is there another way to reach the same target?. I am trying to implement a SP that is going to be called from SSIS.

谢谢

推荐答案

你的主要问题是 @ID NVARCHAR 等价于 @ID NVARCHAR(1) 而你正在被截断.

Your main problem is that @ID NVARCHAR is equivalent to @ID NVARCHAR(1) and you are getting truncation.

此处也不需要游标，应避免使用 sp_ 前缀，因为它是为 Microsoft 系统过程保留的.

You don't need a cursor here either and the sp_ prefix should be avoided as it is reserved for Microsoft system procedures.

以下将返回输出

,OR ID ='101075330-IC001',OR ID ='ACP-2582785',OR ID ='ACP-645655',ORID='ACP-942612'

,OR ID ='101075330-IC001',OR ID ='ACP-2582785',OR ID ='ACP-645655',OR ID ='ACP-942612'

来自您的示例输入

CREATE PROCEDURE dbo.usp_IDENTIF (@ID NVARCHAR(MAX) OUTPUT)
AS
  BEGIN
      WITH T(ID)
           AS (SELECT ID
               FROM   TABLE_1
               WHERE  ID IS NOT NULL
               UNION
               SELECT ID
               FROM   TABLE_2
               WHERE  ID IS NOT NULL)
      SELECT @ID = CAST((SELECT CONCAT(',OR ID =''', ID, '''')
                         FROM   T
                         FOR XML PATH('')) AS NVARCHAR(MAX))
  END 

这篇关于sql中执行一个sp的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！