How do I put the result of stored procedure in a new column in a view?

Question

I have created a stored procedure which takes as inputs a schema name and a table name and outputs an INT containing the largest value in the id column of the table. I have also created a view containing all the schema names, table names, and primary key column names in my database. I would like to add a column to this view containing the result of my stored procedure, using the schema name and table name values as inputs to the row’s stored procedure. I have tried a few different things, but I can’t find a way to do it.

Stored procedure:

CREATE PROCEDURE [dbo].[sp_ttw_max_id]
(
	@schema VARCHAR(50),
	@table VARCHAR(50),
	@max_id INT OUTPUT
) AS
BEGIN
    -- Another custom stored procedure to help sanitize the input
	EXEC sp_ttw_santize @schema, @table, @schema OUTPUT, @table OUTPUT;

	DECLARE @param NVARCHAR(255); 
    SET @param = '@max_id_param INT OUTPUT';

	DECLARE @sql NVARCHAR(255); 
    SET @sql = 'SELECT TOP 1 @max_id_param = [id]
				FROM ' + @table + '
				ORDER BY id DESC;'

	EXEC sp_executesql @sql, @param, @max_id_param = @max_id OUTPUT;
END;

View:

CREATE VIEW [dbo].[pk_columns] 
AS
    SELECT 
        col_cnst.TABLE_SCHEMA, col_cnst.TABLE_NAME, COLUMN_NAME
	FROM
        INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col_cnst
	JOIN 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tbl_cnst ON col_cnst.CONSTRAINT_NAME = tbl_cnst.CONSTRAINT_NAME
	WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY';

My attempts:

First, I tried adding `@max_id` to the column list in the view’s `SELECT` statement:

DECLARE @max_id INT;

SELECT col_cnst.TABLE_SCHEMA, col_cnst.TABLE_NAME, COLUMN_NAME, @max_id...

This put a column of nulls on my view. Unfortunately, I can’t tell how to actually assign the value. Replacing the parameter name with my EXEC statement didn’t help.

Second, I tried to add change the view into a temporary table and put a DEFAULT constraint on my new column, but I had the same problem.

Third, I tried modifying the stored procedure to not return the max ID as an OUTPUT parameter. I replaced all the @max_ids in my earlier attempts with EXEC, but still no good.

Is this even possible?

asked 2021-10-17 by JDCAce

---

Answer

You cannot include the output of a stored procedure in a view.

However, in this case, it seems like you can get the same data a different way. Rather than using dynamic SQL to query a table directly, you can access the metadata in sys.identity_columns.

DECLARE @schema nvarchar(128),
    @table nvarchar(128)

SELECT SchemaName = s.name,
         TableName = o.name,
         LastValue = ic.last_value,
         IncrementValue = ic.increment_value
FROM sys.identity_columns AS ic
JOIN sys.objects AS o ON o.object_id = ic.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = @schema
AND o.name = @table;

By querying the metadata directly, you can now incorporate the query into your view by adding it with the appropriate joins or subquery. It will additionally be significantly faster than querying the table directly. In your case, if you just want a list of all identity columns (assuming every ID is a Primary Key), the above query (with the variables removed) seems to get you pretty much the output you’re trying to get.

Also note that schema and table names can contain Unicode characters and be up to 128 characters long. You should ensure that your input parameters support Unicode (Nvarchar), and are sufficiently long (128).

There’s a minor difference with regard to sys.identity_columns.last_value and MAX(schema.table.id), which is that an ID value could be consumed (via an insert) that is subsequently rolled back or deleted. This will result in a gap in the ID values in the table, and that gap is reflected when sys.identity_columns.last_value and MAX(schema.table.id) are out of sync. In any forward-looking scenario, the last_value will be most correct, though if you are specifically looking back to identify the “highest committed value that has currently exists in the table” then you would need to calculate the MAX() to be 100% sure the returned value exists.

answered 2021-10-17 by Andy Mallon