Understanding Stored Procedures and Views

As a developer, it’s common to work with stored procedures and views in database management systems. A stored procedure is a precompiled SQL statement that can be executed multiple times from different parts of your program. On the other hand, a view is a virtual table based on the result of a query.

In this article, we’ll explore how to put the result of a stored procedure in a new column of a view.

Problem Statement

We have created a stored procedure sp_ttw_max_id that takes two parameters: @schema and @table. The procedure outputs an integer containing the largest value in the id column of the specified table. We’ve also created a view pk_columns that contains all schema names, table names, and primary key column names.

Our goal is to add a new column to this view containing the result of our stored procedure. However, we’re facing challenges in achieving this.

Attempting to Use Stored Procedure Output

We first tried adding the output of the stored procedure to the column list of the view’s SELECT statement:

DECLARE @max_id INT;

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

However, this approach resulted in a column of null values.

Alternative Approach: Using Metadata

As suggested by the answer, we can access metadata directly from sys.identity_columns instead of querying the table directly using dynamic SQL.

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;

Incorporating Stored Procedure Output into the View

We can now incorporate the query into our view by adding it with the appropriate joins or subquery.

SELECT 
    col_cnst.TABLE_SCHEMA, 
    col_cnst.TABLE_NAME, 
    COLUMN_NAME,
    (SELECT top 1 max_id FROM sp_ttw_max_id(@schema, @table)) AS max_id
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';

Notes on Unicode Characters and Input Parameters

When working with schema and table names, be sure to use Nvarchar parameters that support Unicode characters.

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;

Conclusion

Incorporating the output of a stored procedure into a view can be achieved by using metadata directly and adding the query to the view’s SELECT statement.

The final code snippet demonstrates this approach:

-- Create the stored procedure
CREATE PROCEDURE sp_ttw_max_id @schema nvarchar(128), @table nvarchar(128)
RETURNS INT AS
BEGIN
    DECLARE max_id INT;
    SELECT top 1 max_id FROM sys.identity_columns WHERE object_id = OBJECT_ID(@schema + '.' + @table) AND column_id > 0;
    RETURN max_id;
END;

-- Create the view
CREATE VIEW pk_columns
AS
SELECT 
    col_cnst.TABLE_SCHEMA, 
    col_cnst.TABLE_NAME, 
    COLUMN_NAME,
    (SELECT top 1 max_id FROM sp_ttw_max_id(col_cnst.TABLE_SCHEMA, col_cnst.TABLE_NAME)) AS max_id
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';

This code creates a stored procedure sp_ttw_max_id that returns the maximum value in an identity column of a specified table. The view pk_columns incorporates this query using metadata directly.

Performance Considerations

Querying the metadata directly is significantly faster than querying the table directly using dynamic SQL.

Last modified on 2023-09-23