Creates a function in SQL Server that accepts a parameter (user_name) and returns a table

Question

I want to enter the following code into a function that will accept one parameter (User_name) and return the table.

Below is the code:

DECLARE @_l_Table_Data nvarchar(max) 
DECLARE @user_name nvarchar(max) 

SET @user_name= (SELECT USER_NAME FROM sysaid_user WHERE user_name = @user_name) 
SET @_l_Table_Data = (SELECT user_cust_json FROM sysaid_user WHERE user_name = 'CONSIST-SYS\alonb')

SELECT *
FROM OPENJSON(@_l_Table_Data)
     WITH (
              ci_id   NVARCHAR(max) '$.ci_id' ,
              amount  NVARCHAR(max) '$.amount',
              user_n  NVARCHAR(max) '$.user_name'                  
          );

Answer 1

Typical Functions in SQL Server are scalar, returning a single value. If you need rows/columns then you can use a table-valued function.

I cannot test this as you provided no test data, but you could try something like the following:

CREATE FUNCTION dbo.FetchCustom (   
    @user_name NVARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN (

    SELECT
        jsn.ci_id, jsn.amount, jsn.user_n 
    FROM sysaid_user AS usr
    CROSS APPLY (

        SELECT *
        FROM OPENJSON ( usr.user_cust_json )
        WITH (
            ci_id   NVARCHAR(max) '$.ci_id' ,
            amount  NVARCHAR(max) '$.amount',
            user_n  NVARCHAR(max) '$.user_name'                  
        )

    ) AS jsn
    WHERE 
        usr.[USER_NAME] = @user_name

)
GO

A basic call to use the function would be:

SELECT * FROM dbo.FetchCustom ( 'CONSIST-SYS\alonb' );

Also, what's with all the NVARCHAR(MAX)? Do you really have username that requires the use of MAX? I seriously doubt it. A good practice is to limit your sizes to the max they could contain (e.g., NVARCHAR(50)).

UPDATE:

If the usr.user_cust_json contains another key that is actually dynamic For example: {"data": {"4251":{"ci_id":"4251","amount":"-50","user_name":"AssetStorage"} ,"4289":{"ci_id":"4289","amount":"-8","user_name":"AssetStorage"} ,"4280":{"ci_id":"4280","amount":"-1","user_name":"AssetStorage"}} } How can this key be displayed in this function? What change does this require?

To include the key value in your resultset you can modify the TVF like so:

CREATE FUNCTION dbo.FetchCustom (   
    @user_name NVARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN (
    
    SELECT
        jsn.[key], jsn.ci_id, jsn.amount, jsn.user_n 
    FROM sysaid_user AS usr
    CROSS APPLY (
    
        SELECT
            [key], ci_id, amount, user_n
        FROM OPENJSON ( usr.user_cust_json, '$.data' ) AS k
        CROSS APPLY (
    
            SELECT
                ci_id, amount, user_n
            FROM OPENJSON ( k.[value] ) WITH (
                ci_id   NVARCHAR(max) '$.ci_id' ,
                amount  NVARCHAR(max) '$.amount',
                user_n  NVARCHAR(max) '$.user_name'
            )

        ) AS d
    
    ) AS jsn
    WHERE 
        usr.[USER_NAME] = @user_name
    
)
GO

If you were to run the following in SSMS you can see the underlying resultset based in the JSON you provided.

DECLARE @json VARCHAR(1000) = '{"data": {"4251":{"ci_id":"4251","amount":"-50","user_name":"AssetStorage"} ,"4289":{"ci_id":"4289","amount":"-8","user_name":"AssetStorage"} ,"4280":{"ci_id":"4280","amount":"-1","user_name":"AssetStorage"}}}';

SELECT
    [key], ci_id, amount, user_n
FROM OPENJSON ( @json, '$.data' ) AS k
CROSS APPLY (
    
    SELECT
        ci_id, amount, user_n
    FROM OPENJSON ( k.[value] ) WITH (
        ci_id   NVARCHAR(max) '$.ci_id' ,
        amount  NVARCHAR(max) '$.amount',
        user_n  NVARCHAR(max) '$.user_name'
    )

) AS d;

Returns

+------+-------+--------+--------------+
| key  | ci_id | amount |    user_n    |
+------+-------+--------+--------------+
| 4251 |  4251 |    -50 | AssetStorage |
| 4289 |  4289 |     -8 | AssetStorage |
| 4280 |  4280 |     -1 | AssetStorage |
+------+-------+--------+--------------+