About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Monday, July 12, 2010

CRUD Stored Procedures from Metadata – Part I: The 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:
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'


    Here’s the output:
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

1 comment:

Nilesh Gambhava said...

This is really gr8 stuff..

But when we want to do different configurations on different databases then this becomes bit difficult.

Can any one please suggest good <a href="http://www.tools4sql.net>CRUD Stored Procedure Creator</a>?