CODEWITHSUNDEEP
sql-server-2008stored-proceduresexport
SRahmani
SRahmani

Reputation: 359

Quick Help on stored procedure .adding headers to output

How can I modify this stored procedure to output the headers in the export? It works with the bcp utility when assigned to a SQL Server Agent but I need to have the file also export out the headers in the file (as well as the contents of the select)

CREATE PROCEDURE [dbo].[GL_Export] 
AS
    DECLARE @gl table 
   (AccCode VARCHAR(3000),
    CodeDesc VARCHAR(3000),
    AccountType_id VARCHAR(3000),
    AccGroupName VARCHAR(3000)
   )

  -- Inserts data into @temptable (memory) 
  INSERT  @gl
    SELECT DISTINCT 
        RTRIM (s1.SGMNTID) AS 'AccCode',RTRIM (s1.DSCRIPTN) AS 'CodeDesc', 
        CASE
           WHEN s1.SGMTNUMB = '1' THEN '1' 
           WHEN s1.SGMTNUMB = '2' THEN '2'
           WHEN s1.SGMTNUMB = '3' THEN '110'
           WHEN s1.SGMTNUMB = '4' THEN '4'
           WHEN s1.SGMTNUMB = '5' THEN '120'
        END AS 'AccountType_id',
        CASE 
           WHEN s1.SGMTNUMB = '2' THEN LEFT(s1.SGMNTID, 2)
           ELSE 'DEFAULT'
        END AS 'AccGroupName'
    FROM 
        GL40200 s1

    UNION 

    SELECT  
        REPLACE ([ACTNUMBR_1] + '-' + [ACTNUMBR_2] + '-'
                 + [ACTNUMBR_3] + '-' +[ACTNUMBR_4] + '-'
                 + [ACTNUMBR_5], ' ', '') AS 'AccCode',
        '' AS 'CodeDesc',
        '0' AS 'AccountType_id',
        'Default' AS 'AccGroupName'
    FROM 
        GL00100 a

    -- Selects the output for memory and should add headers to file
    SELECT
        AccCode, CodeDesc, AccountType_id, AccGroupName 
    FROM @gl

    UNION


    Select AccCode,CodeDesc,AccountType_id,AccGroupName from @gl


    GO

Upvotes: 1

Views: 373

Answers (1)

Darth Continent
Darth Continent

Reputation: 2319

You could use UNION ALL and have one SELECT that has the column headings, and then another with the actual values cast to varchar as appropriate:

SELECT 'AccCode', 'CodeDesc', 'AccountType_id', 'AccGroupName'

UNION ALL

SELECT
    CAST(AccCode AS varchar(100)), 
    CAST(CodeDesc AS varchar(100)),
    CAST(AccountType_id AS varchar(100)),
    CAST(AccGroupName AS varchar(100))
FROM @gl

(Of course, if some columns are already non-numeric you could leave them as such, otherwise CAST as appropriate.)

Upvotes: 1

Related Questions