title: SQL Server table to json
body: <p>I am looking to pull some columns (Col1 and 2) of a table and put in JSON format and also write some hardcoded JSON in each node, like this. </p>
  
  <blockquote>
    <p>{ "col1":"xxxx", "col2":"xxxx", "hardcodedString":"xxxx",
    "hardcodedString":"xxxx", "hardcodedString":"xxxx",
    "hardcodedString":"xxxx", "hardcodedString":"xxxx"},</p>
  </blockquote>
  
  <p>I found the following git script, it creates a SP that should generate JSON but when i executed as required i get 'Commands Completed Succesfully'</p>
  
  <p>Any ideas where the output is going or indeed if a better way to acheive my JSON?</p>
  
  <pre><code>create procedure [dbo].[GetJSON] (
      @schema_name varchar(50),
      @table_name varchar(50),
      @registries_per_request smallint = null
  )
  as
  begin
      if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and table_name = @table_name ) &gt; 0 )
      begin
          declare @json varchar(max),
              @line varchar(max),
              @columns varchar(max),
              @sql nvarchar(max),
              @columnNavigator varchar(50),
              @counter tinyint,
              @size varchar(10)
  
          if (@registries_per_request is null)
          begin
              set @size = ''
          end
          else
          begin
              set @size = 'top ' + convert(varchar, @registries_per_request)
          end
          set @columns = '{'
  
          declare schemaCursor cursor for
              select column_name
              from information_schema.columns
              where table_schema = @schema_name
              and table_name = @table_name
          open schemaCursor
  
          fetch next from schemaCursor into @columnNavigator
  
          select @counter = count(*)
          from information_schema.columns
          where table_schema = @schema_name
          and table_name = @table_name
  
          while @@fetch_status = 0
          begin
              set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
              set @counter = @counter - 1
              if ( 0 != @counter )
              begin
                  set @columns = @columns + ','
              end
  
              fetch next from schemaCursor into @columnNavigator
          end 
  
          set @columns =  @columns + '}'
  
          close schemaCursor
          deallocate schemaCursor
  
          set @json = '['
  
          set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from [' + @schema_name + '].[' + @table_name + ']'
          exec sp_sqlexec @sql
  
          select @counter = count(*) from tmpJsonTable
  
          declare tmpCur cursor for
              select * from tmpJsonTable
          open tmpCur
  
          fetch next from tmpCur into @line
  
          while @@fetch_status = 0
          begin
              set @counter = @counter - 1
              set @json = @json + @line
              if ( 0 != @counter )
              begin
                  set @json = @json + ','
              end
  
              fetch next from tmpCur into @line
          end
  
          set @json = @json + ']'
  
          close tmpCur
          deallocate tmpCur
          drop table tmpJsonTable
  
          select @json as json
      end
  end
  </code></pre>