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
