SQL Reloaded: CRUD Stored Procedures from Metadata II (Get, Dropdown & Delete)

    If in the previous post on CRUD Stored Procedures from Metadata I shown how could be created automatically a stored procedure for Insert/Update, in this post I’ll show how a similar template could be created in order to create a Get, Dropdown or Delete stored procedure. The so called “Get” stored procedure is used in order to retrieve a record for view/update, and here’s the template for it:  

-- Template for Get stored procedure 
CREATE FUNCTION dbo.TemplateGet( 
@SchemaName nvarchar(50) 
, @TableName nvarchar(50) 
, @id nvarchar(50) 
, @id_data_type nvarchar(50) 
, @attributes nvarchar(max) 
) 
RETURNS nvarchar(max) 
AS  
BEGIN 
SET @attributes = Right(@attributes, Len(@attributes) - CharIndex(',', @attributes)-1) 
RETURN( 'CREATE PROCEDURE ' + @SchemaName + '.pGet' + @TableName + '(' + CHAR(13)+ char(10) +  
     '@' + @ID + ' ' + @id_data_type + ')' + CHAR(13)+ char(10) +  
     'AS' + CHAR(13)+ char(10) +  
     'BEGIN' + CHAR(13)+ char(10) +  
     ' SELECT ' + @attributes + CHAR(13)+ char(10) +  
     ' FROM ' + @SchemaName + '.' + @TableName + CHAR(13)+ char(10) +  
     ' WHERE ' + @ID + '= @' + @ID + CHAR(13)+ char(10) +  
     'END') 
END 

    Here’s the code to generate the Get stored procedure for Person.Address table, its output, respectively the code to test it: 

-- Get stored procedure creation 
 SELECT dbo.TemplateGet( 
[Schema_Name] , Table_Name  
, id  
, id_data_type  
, [attributes]) CodeSP 
FROM dbo.vCRUDMetadata 
WHERE [Schema_Name] = 'Person' 
AND Table_Name = 'Address' 

 
-- the created Get stored procedure 
CREATE PROCEDURE Person.pGetAddress( 
@AddressID int) 
AS 
BEGIN SELECT AddressLine1 , AddressLine2 
, City 
, StateProvinceID 
, PostalCode 
, rowguid 
, ModifiedDate 
FROM Person.Address 
WHERE AddressID= @AddressID 
END 

-- testing the Get stored procedure 
EXEC Person.pGetAddress 1 

    A similar template could be generated also for deletions:

-- Template for Delete stored procedure 
CREATE FUNCTION dbo.TemplateDelete( 
@SchemaName nvarchar(50) 
, @TableName nvarchar(50) 
, @id nvarchar(50) 
, @id_data_type nvarchar(50)) 
RETURNS nvarchar(max) 
AS BEGIN 
RETURN ( 'CREATE PROCEDURE ' + @SchemaName + '.pDelete' + @TableName + '(' + CHAR(13)+ char(10) +  
'@' + @ID + ' ' + @id_data_type + ')' + CHAR(13)+ char(10) +  
     'AS' + CHAR(13)+ char(10) +  
     'BEGIN' + CHAR(13)+ char(10) +  
     ' DELETE FROM ' + @SchemaName + '.' + @TableName + CHAR(13)+ char(10) +  
     ' WHERE ' + @ID + '= @' + @ID + CHAR(13)+ char(10) +  
     'END') 
END 

    Here’s the code to generate the Delete stored procedure for Person.Address table, its output, respectively the code to test it: 
 

-- Delete stored procedure creation 
 SELECT dbo.TemplateDelete( 
[Schema_Name] , Table_Name  
, id  
, id_data_type) CodeSP 
FROM dbo.vCRUDMetadata 
WHERE [Schema_Name] = 'Person' 
AND Table_Name = 'Address' 

-- the created Delete stored procedure 
CREATE PROCEDURE Person.pDeleteAddress( 
@AddressID int) 
AS 
BEGIN DELETE FROM Person.Address 
WHERE AddressID= @AddressID 
END           

 -- testing the Delete stored procedure 
 EXEC Person.pDeleteAddress 1   

    The Dropdown stored procedure, how its name denotes, it returns the list of values needed in order to populate a dropdown, usually the ID and the Name or Description behind the respective ID. The ID is already stored in the view, though the Name behind the ID could be a little trickier to get, though not impossible. For example many data models use the “Name” and “Description” ad literam, while in some cases the table name. AdventureWorks database uses the first technique, so here’s the template, its output, respectively the code to test it:     

-- Template for Dropdown stored procedure 
CREATE FUNCTION dbo.TemplateDropdown( 
@SchemaName nvarchar(50) 
, @TableName nvarchar(50) 
, @id nvarchar(50)) 
RETURNS nvarchar(max) 
AS  
BEGIN 
RETURN( 'CREATE PROCEDURE ' + @SchemaName + '.pDropdown' + @TableName + CHAR(13)+ char(10) +  
     'AS' + CHAR(13)+ char(10) +  
     'BEGIN' + CHAR(13)+ char(10) +  
     ' SELECT ' + @ID + CHAR(13)+ char(10) +  
     ' , Name AS ' + @TableName + CHAR(13)+ char(10) +  
     ' FROM ' + @SchemaName + '.' + @TableName + CHAR(13)+ char(10) +  
     ' ORDER BY Name ' + CHAR(13)+ char(10) +  
     'END') 
END  

-- Dropdown stored procedure creation 
SELECT dbo.TemplateDropdown( 
[Schema_Name] 
, Table_Name  
, id) CodeSP 
FROM dbo.vCRUDMetadata 
WHERE [Schema_Name] = 'Production' 
AND Table_Name = 'ProductSubcategory' 

-- the created Dropdown stored procedure 
CREATE PROCEDURE Production.pDropdownProductSubcategory 
AS 
BEGIN SELECT ProductSubcategoryID 
, Name AS ProductSubcategory 
FROM Production.ProductSubcategory 
ORDER BY Name  
END       
        
-- testing the Dropdown stored procedure
EXEC Production.pDropdownProductSubcategory 

   The stored procedures could be adapted to specific requirements. Please note they are not necessarily designed for general purposes but just as an example on how the creation of stored procedures could be automated. Sometimes it depends also on whether the database was designed for this purpose – for example in the data models I built the UID is always the first attribute in a table, the first parameter in a stored procedure and so on. Usually I’m trying to built a template which could be easier modified for other purposes using copy paste or semiautomated methods, thus attempting to eliminate as much as possible the repetitive tasks. The templates could be modified to enforce special formatting or to include metadata.  

     In general I’m creating also a stored procedure for searching within a table, and as I encapsulate the logic in a view, in theory the view metadata could be used to generate the respective stored procedure in a semiautomatic manner too.

Happy Coding!