confusion with quotes and double quotes in a query

i have a query that works beautifully:

CREATE Procedure BCP_Text_File
(  
    @table varchar(100),  
    @FileName varchar(100)  
)  
AS
    If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000)  
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''  
        Exec(@str)  
    end
    else
        Select 'The table '+@table+' does not exist in the database'

but i need to add this in there:

select column_name 
from information_schema.columns
where table_name = @table
order by ordinal_position

so far i have:

   alter Procedure BCP_Text_File
   (  
      @table varchar(100),  
      @FileName varchar(100)  
    )  
    AS 
       If exists(Select * from information_Schema.tables where table_name=@table)
       Begin
           Declare @str varchar(1000)  
           set @str='Exec Master..xp_Cmdshell ''bcp "

           select column_name 
           from information_schema.columns
           where table_name = '+db_name()+'..'+@table+'
           order by ordinal_position

           Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''  
           Exec(@str)  
       end
       else
           Select 'The table '+@table+' does not exist in the database'

but i think i am missplacing the single quotes and/or double quotes. i am adding this select statement so that my result has the field names as the first row.

thanks so much for any help or guidance.

Answers


Perhaps this is what you want? This assumes that (a) none of your column names have commas in them, and (b) the output of each column, when implicitly converted to a string, is okay.

ALTER PROCEDURE dbo.BCP_Text_File
    @table    NVARCHAR(255),  
    @filename VARCHAR(100)  
AS
BEGIN
  SET NOCOUNT ON;

  IF OBJECT_ID(@table) IS NOT NULL
  BEGIN
    DECLARE 
        @sql NVARCHAR(MAX), 
        @cols NVARCHAR(MAX) = N'';

    SELECT @cols += ',' + name
      FROM sys.columns
      WHERE [object_id] = OBJECT_ID(@table)
      ORDER BY column_id;

    SELECT @cols = STUFF(@cols, 1, 1, '');

    SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT ''''' 
        + REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT ' 
        + 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM ' 
        + DB_NAME() + '..' + @table + '" queryout "' + @filename + '" -c''';  

    EXEC sp_executesql @sql;
  END
  ELSE
  BEGIN
    SELECT 'The table '+@table+' does not exist in the database';
  END
END
GO

But I have to agree with the advice you've gotten from others on this and other questions - this approach is very brittle. You're trying to crack open a pistachio with a steamroller.

PS I removed references to INFORMATION_SCHEMA, because I think the catalog views are more reliable and more consistent.


Need Your Help

What is preferable to put authentication rules, in the site web config or in a web config file specific to each folder?

asp.net web-config authorization

I have a site with a number of pages in different folders. The pages should be available to users according to their roles. This is defined by rules defined in the authorization tags in the web con...

Why do I only see the last value from a List in my RichTextBox?

c# winforms

Why is this code only outputting one element from the list?