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 II: The Get, Dropdown and 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:
-- 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'


    And here’s the output:
-- 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
And the code to test it:
-- 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:
-- 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'


   And here’s the output:
-- the created Delete stored procedure
CREATE PROCEDURE Person.pDeleteAddress(
@AddressID int)

AS
BEGIN
DELETE FROM Person.Address
WHERE AddressID= @AddressID

END
    And the code to test it:
-- 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:
-- 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

    Here’s the code to generate the Get stored procedure for Person.Address table:
-- Dropdown stored procedure creation
SELECT dbo.TemplateDropdown(
[Schema_Name]

,
Table_Name

,
id) CodeSP

FROM
dbo.vCRUDMetadata

WHERE
[Schema_Name] = 'Production'

AND Table_Name = 'ProductSubcategory'


    And here’s the output:
-- the created Dropdown stored procedure
CREATE PROCEDURE Production.pDropdownProductSubcategory
AS
BEGIN
SELECT ProductSubcategoryID
, Name AS ProductSubcategory
FROM Production.ProductSubcategory
ORDER BY Name

END

    And the code to test it:
-- 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.

No comments: