1

This SQL Server stored procedure returns a string with values delimited by single commas and placing an apostrophe before each value. Uses a table named Sucursal and returns an string with all branches assigned to an specific people or manager:

Please, let me to asume following values into Sucursal table:

SELECT Sc_Cve_Sucursal, Sc_Gerente
             FROM   dbo.Sucursal
             WHERE  Sc_Gerente = 'CCANTO'
                    AND Es_Cve_Estado <> 'BA';

Returns:

Sc_Cve_Sucursal|Sc_Gerente
==========================
0013           |CCANTO    
0014           |CCANTO    
0015           |CCANTO    
0018           |CCANTO    
0022           |CCANTO    
0024           |CCANTO    
0025           |CCANTO    
0026           |CCANTO    
0027           |CCANTO    
0028           |CCANTO    
0030           |CCANTO    
0031           |CCANTO    
0033           |CCANTO    
0041           |CCANTO    
0052           |CCANTO    
0055           |CCANTO    
0060           |CCANTO    
0063           |CCANTO    
0078           |CCANTO    
D4             |CCANTO

If you need all values into the table in a single delimited string, you need following stored procedure or SQL script:

CREATE PROCEDURE ZMCL_SCGM_Get_SucursalesXSupervisor
    (@SUPERVISOR AS NVARCHAR(10))
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @SUCURSALES AS NVARCHAR(MAX);

    SELECT @SUCURSALES = COALESCE(@SUCURSALES + CHAR(39) + ', ' + CHAR(39),'') + Sc_Cve_Sucursal
    FROM dbo.Sucursal
    WHERE Sc_Gerente = @SUPERVISOR
      AND Es_Cve_Estado <> 'BA';

    SELECT CHAR(39) + @SUCURSALES + CHAR(39);
END;
GO

This returns a string like this:

'0013', '0014', '0015', '0018', '0022', '0024', '0025', '0026', '0027', '0028', '0030', '0031', '0033', '0041', '0052', '0055', '0060', '0063', '0078', 'D4'

I hope this SQL script will be useful. I'm using to use the returned string with the INTO command (to return all branches assigned to an specific manager):

C# code:

var sql = string.Concat("SELECT ", Fields, " FROM ", TableName);
sql += " WHERE cvesucursal IN (@CveSucursal)";

@CveSucursal has the values returned by previous SQL script.

César Qüeb
  • 121
  • 7
  • 1
    What is your question? –  Nov 05 '16 at 07:25
  • This is my reply or answer to the question placed here: http://stackoverflow.com/questions/5196371/sql-query-concatenating-results-into-one-string For restrictions, I can't place the reply on that thread. Placing because can be useful for some dude that requires an string with values from a field of a table, delimited by commas and placed into apostrophes. – César Qüeb Nov 05 '16 at 15:47
  • You should add answers to the question, not create a new question –  Nov 05 '16 at 16:06

1 Answers1

0

I'm confused. Why not just write one query?

SELECT <Fields>
FROM <TableName>
WHERE cvesucursal IN (SELECT Sc_Cve_Sucursal
                      FROM dbo.Sucursal
                      WHERE  Sc_Gerente = @SUPERVISOR AND Es_Cve_Estado <> 'BA
                     );

You don't need to store the intermediate values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786