TSQL stored procedure: convert 1 row from any table to a columnName columnValue table-CodePudding

Goal

I'm trying to create a stored procedure that would take @tableName VARCHAR(MAX) and @id INT and return a list of key-value pairs, that is, a table with 2 columns called ColumnName VARCHAR(MAX) and ColumnValue VARCHAR(MAX) which would be populated with column names and values from a simple query that selects all columns from @tableName where [Id] = @id.

Example

Let's say we have a table

Person

Id	FirstName	LastName	Nickname
1	John	Johnson	Jonny
2	Sally	Johnson	Sal
...

and then we call the stored procedure

EXEC spConvertToKeyValuePairs 'Person', 2

and the output of the stored procedure would be

ColumnName	ColumnValue
Id	2
FirstName	Sally
LastName	Johnson
Nickname	Sal

What I made so far

I got to the point where I have column names

CREATE PROCEDURE [dbo].[spConvertToKeyValuePairs]
    @tableName VARCHAR(MAX),
    @id INT
AS
    DECLARE @valTable TABLE(ColumnName VARCHAR(MAX), ColumnValue VARCHAR(MAX))

    INSERT INTO @valTable (ColumnName, ColumnValue)
        SELECT [COLUMN_NAME] 
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE [TABLE_NAME] = @tablename

    SELECT * FROM @valTable

    -- and now I don't know how to add row values

RETURN 0

We are not talking about ___ in this question

Tables that don't have Id INT PRIMARY KEY IDENTITY(1,1) column
Security risks

Question

Can you help me finish / redesign this stored procedure to get the output I need?

CodePudding user response：

A static version using JSON functions

select b.[Key] col, b.[Value] value
from yourTable a
cross apply OpenJson(
  (select a.* 
   for JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)  
) b
where Id = @id

As you said, you should build a dynamic sql and use sp_executesql to run it with a parameter.

Demo