SQL Reloaded: CRUD Stored Procedures from Metadata I (Insert & Update)

    In Saving Data With Stored Procedures post I shown my favorite technique of creating a new record, respectively updating an existing record, by using a stored procedure, while in Creating a Stored Procedure from Table’s Metadata post I shown how a macro could be created in a semiautomatic manner in order to create the same type of stored procedure using table’s metadata. The Excel approach, even if decreases the creation time for a stored procedure to only a few minutes, has the disadvantage that the metadata has to be copied in Excel manually for each table (when manual work is involved in automatic generation of code we deal with semiautomatic methods). Last weekend, after I had to use the macro on Friday at work to create such a stored procedure, I was thinking on whether is possible to achieve the same using only T-SQL code. The difficulty of such a task resides primarily in creating the repeating code for inserting and updating the tables, and for creating the stored procedure parameters, fact that could be achieved with a cursor or a common table expression. On a secondary place could be put the handling of special data types like uniqueidentifier, xml, date/time or even bit. The good news is that not all data types are used in data modeling, at least not in the same proportions. In practice, when attempting to automate tasks, treating all the possible cases could equate with quite an effort, which maybe doesn’t pay off, therefore is important to find the balance between effort and utility (sometimes automating 60-70% of an work is quite an achievement, while other times 90-100% could be achieved). Here’s is the view I created for this task.

-- prepares metadata for CRUD stored procedures 
CREATE VIEW dbo.vCRUDMetadata 
AS 
WITH CTE([schema_name], Table_Name, id, id_data_type, Column_id, [parameters], [values], [attributes], [update]) 
AS 
( SELECT A.[schema_name] 
, A.Table_Name 
, A.Column_Name id 
, A.system_type id_data_type 
, A.Column_id  
, Cast(' @' + A.Column_Name + ' ' + system_type  
+ CASE  
     WHEN A.system_type IN ('char', 'nchar' , 'varchar', 'nvarchar') THEN ' (' + CASE A.max_length WHEN -1 THEN 'max' ELSE Cast(A.max_length AS varchar(10)) END + ')'  
      ELSE '' 
END  
+ ' output' as nvarchar(max)) [parameters] 
, Cast(CASE  
WHEN A.system_type = 'bit' THEN ' IsNull(@' + A.Column_Name + ', 0)'  
WHEN A.system_type = 'uniqueidentifier' THEN ' IsNull(@' + A.Column_Name + ', NEWID())' 
ELSE '@' + A.Column_Name  
END as nvarchar(max)) [values] 
, Cast(A.Column_Name as nvarchar(max)) [attributes] 
, Cast('' as nvarchar(max)) [update] 
FROM dbo.vTableColumns A 
WHERE A.column_id = 1 
UNION ALL 
SELECT A.[schema_name] 
, A.Table_Name 
, B.id 
, B.id_data_type 
, A.column_id 
, B.[parameters] + CHAR(13)+ char(10)  
+ ', @' + A.Column_Name + ' ' + A.system_type  
+ CASE  
WHEN A.system_type IN ('char', 'nchar' , 'varchar', 'nvarchar') THEN ' (' + CASE A.max_length WHEN -1 THEN 'max' ELSE Cast(A.max_length AS varchar(10)) END + ')'  
ELSE '' 
END [parameters] 
, B.[values] + CHAR(13)+ char(10)  
+ ', ' + CASE  
WHEN A.system_type = 'bit' THEN ' IsNull(@' + A.Column_Name + ', 0)'  
WHEN A.system_type = 'uniqueidentifier' THEN ' IsNull(@' + A.Column_Name + ', NEWID())' 
ELSE '@' + A.Column_Name  
END [values] 
, B.[attributes] + CHAR(13)+ char(10) + ', ' + A.Column_Name [attributes] 
, B.[update] + CHAR(13)+ char(10)  
+ ', ' + A.Column_Name + ' = ' 
+ CASE  
WHEN A.Column_Name = 'bit' THEN ' IsNull(@' + A.Column_Name + ', 0)'  
WHEN A.system_type = 'uniqueidentifier' THEN ' IsNull(@' + A.Column_Name + ', NEWID())' 
ELSE '@' + A.Column_Name  
END [update] 
FROM dbo.vTableColumns A 
JOIN CTE B 
ON A.column_id - 1 = B.column_id 
AND A.[schema_name] = B.[schema_name] 
AND A.Table_Name = B.Table_Name 
) SELECT A.[schema_name] 
, A.Table_Name 
, A.column_id NumberAttributes 
, A.id 
, A.id_data_type 
, A.[parameters] 
, A.[values] 
, A.[attributes] 
, A.[update] 
FROM CTE A 
WHERE column_id IN ( SELECT MAX(column_id) 
FROM CTE B 
WHERE A.[schema_name] = B.[schema_name] 
AND A.Table_Name = B.Table_Name) 

    The view needs additional work because it treats only the most simple met cases, preferring to keep it (relatively) simple. Even so, the logic might be not so simple to understand within a simple review - it just makes use of CASEs combined with concatenation, building a string for parameters, update, insert or select.

    Here’s a call to the view for a table:

-- testing the view 
SELECT * 
FROM dbo.vCRUDMetadata 
WHERE [Schema_Name] = 'Person' 
AND Table_Name = 'Address' 

    Now how do we make it useful? I used the same idea as in Excel, the code for creating the Save stored procedure being implemented in the below UDF:

-- Template for Save stored procedure 
CREATE FUNCTION dbo.TemplateSave( 
@SchemaName nvarchar(50) 
, @TableName nvarchar(50) 
, @id nvarchar(50) 
, @id_data_type nvarchar(50) 
, @parameters nvarchar(max) 
, @values nvarchar(max) 
, @attributes nvarchar(max) 
, @update nvarchar(max) 
) RETURNS nvarchar(max) 
AS BEGIN 
DECLARE @DefaultValue nvarchar(2) 
SET @DefaultValue = CASE 
WHEN @id_data_type IN ('char', 'nchar' , 'varchar', 'nvarchar') THEN '''' 
ELSE '0' 
END 
SET @values = Right(@values, Len(@values) - CharIndex(',', @values)-1) 
SET @attributes = Right(@attributes, Len(@attributes) - CharIndex(',', @attributes)-1) 
SET @update = Right(@update, Len(@update) - CharIndex(',', @update)-1) 
RETURN( 'CREATE PROCEDURE ' + @SchemaName + '.pSave' + @TableName + '(' + CHAR(13)+ char(10) + @parameters + ')' + CHAR(13)+ char(10) +  
     'AS' + CHAR(13)+ char(10) +  
     'BEGIN' + CHAR(13)+ char(10) +  
     'BEGIN TRY' + CHAR(13)+ char(10) +  
     ' IF ISNULL(@' + @ID + ', ' + @DefaultValue + ') = ' + @DefaultValue + CHAR(13)+ char(10) +  
     ' BEGIN' + CHAR(13)+ char(10) +  
     ' -- insert statement' + CHAR(13)+ char(10) +  
     ' INSERT INTO ' + @SchemaName + '.' + @TableName + ' (' + @attributes + ')' + CHAR(13)+ char(10) +  
     ' VALUES (' + @values + ')' + CHAR(13)+ char(10) +  
     ' SET @' + @ID + ' = @@IDENTITY' + CHAR(13)+ char(10) +  
     ' END' + CHAR(13)+ char(10) + 
     ' ELSE' + CHAR(13)+ char(10) + 
     ' BEGIN' + CHAR(13)+ char(10) + 
     ' -- update statement' + CHAR(13)+ char(10) +  
     ' UPDATE ' + @SchemaName + '.' + @TableName + CHAR(13)+ char(10) +  
     ' SET ' + @update + CHAR(13)+ char(10) + 
     ' WHERE ' + @ID + '= @' + @ID + CHAR(13)+ char(10) +  
     ' END' + CHAR(13)+ char(10) + 
    ' SELECT @@Rowcount' + CHAR(13)+ char(10) +  
     'END TRY' + CHAR(13)+ char(10) +  
     'BEGIN CATCH' + CHAR(13)+ char(10) +  
    ' SELECT Cast(ERROR_NUMBER() as varchar(10)) + '':'' + ERROR_MESSAGE()' + CHAR(13)+ char(10) +  
    'END CATCH' + CHAR(13)+ char(10) +  
    'END' ) 
END 

 
    The stored procedure template is slightly modified from the one presented in the previous posts, this time preferring to return the ID in the same parameter used as input, using the select to return the number of records affected. Here’s the test script which creates the stored procedure and its output:

SELECT dbo.TemplateSave( 
[Schema_Name] , Table_Name  
, id  
, id_data_type  
, [parameters]  
, [values]  
, [attributes]  
, [update]) CodeSP 
FROM dbo.vCRUDMetadata 
WHERE [Schema_Name] = 'Person' 
AND Table_Name = 'Address' 

CREATE PROCEDURE Person.pSaveAddress( 
@AddressID int output 
, @AddressLine1 nvarchar (120) 
, @AddressLine2 nvarchar (120) 
, @City nvarchar (60) 
, @StateProvinceID int 
, @PostalCode nvarchar (30) 
, @rowguid uniqueidentifier 
, @ModifiedDate datetime) 
AS 
BEGIN 
BEGIN TRY 
IF ISNULL(@AddressID, 0) = 0 
BEGIN 
-- insert statement 
     INSERT INTO Person.Address ( AddressLine1 
     , AddressLine2 
     , City 
     , StateProvinceID 
     , PostalCode 
     , rowguid 
     , ModifiedDate) 
    VALUES ( @AddressLine1 
     , @AddressLine2 
     , @City 
     , @StateProvinceID 
     , @PostalCode 
     , IsNull(@rowguid, NEWID()) 
     , @ModifiedDate) 
    SET @AddressID = @@IDENTITY 
END 
ELSE 
BEGIN 
     -- update statement 
    UPDATE Person.Address 
    SET AddressLine1 = @AddressLine1 
    , AddressLine2 = @AddressLine2 
    , City = @City 
    , StateProvinceID = @StateProvinceID 
    , PostalCode = @PostalCode 
    , rowguid = IsNull(@rowguid, NEWID()) 
    , ModifiedDate = @ModifiedDate 
     WHERE AddressID= @AddressID 
END 
SELECT @@Rowcount 
END TRY BEGIN CATCH 
     SELECT Cast(ERROR_NUMBER() as varchar(10)) + ':' + ERROR_MESSAGE() 
END CATCH 
END 

 
  The resulted code could be exported to an Excel or text file and then run in Management Studio. And here are two calls to it, one for update, and the other one for insert:

EXEC Person.pSaveAddress 1, 'AddressLine1', 'AddressLine2', 'City', 1 , 'PostalCode', NULL, '20090101' 
EXEC Person.pSaveAddress 0, 'AddressLine100', 'AddressLine2', 'City', 1 , 'PostalCode', '6D1EB969-C5CF-420C-BA5F-1153F4D89373', '20090101' 

    The difference from the Excel version is that it could be run easily for a set of tables and then the stored procedures could be created one by one. A higher level of automation could be achieved by creating a cursor or an SSIS package, and then each stored procedure created with EXEC or sp_executesql stored procedures. The template could be modified after requirements as needed. Eventually the code from template could be included in a CLR function and thus take advantage of some string functionality, but it becomes then more difficult to modify