Problems With The Execution Of A Sp In Sql

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

Solution 1:

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

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

The following would return an output of

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

from your example input

CREATEPROCEDURE dbo.usp_IDENTIF (@ID NVARCHAR(MAX) OUTPUT)
ASBEGINWITH T(ID)
           AS (SELECT ID
               FROM   TABLE_1
               WHERE  ID ISNOTNULLUNIONSELECT ID
               FROM   TABLE_2
               WHERE  ID ISNOTNULL)
      SELECT@ID=CAST((SELECT CONCAT(',OR ID =''', ID, '''')
                         FROM   T
                         FOR XML PATH('')) AS NVARCHAR(MAX))
  END