💎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!   

💎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

💎SQL Reloaded: Evaluating Textual Expressions with CLR UDFs

The developers having experience with scripting languages like VBScript or JavaScript are most probably used with Eval (VBScript/JavaScript) or Execute (VBScript) functions, functions that parse a string evaluating its content to an expression or evaluating a given set of instructions, allowing thus to create and execute code at runtime. In several occasions I needed similar functionality also in T-SQL, having to evaluate a formula stored in a text field or text variable. The latest situation of this type I could recall was when importing a set of BOM (Bill of Materials) data from Oracle into SQL Server. I had two problems then, one related to Oracle and the second to SQL Server. 

As each level in the (Oracle) BOM has a Quantity which needs to be multiplied with the Quantities from the child levels in order to get the total Required Quantity for each level, I needed a method to aggregate the respective values. Unlike in SQL Server, in Oracle (as far I know) there is no built-in functionality for that, however SYS_CONNECT_BY_PATH function (see example) allows to built a string with the total quantities available in the BOM structure and is even possible to evaluate the resulted string using a function, though as I was not allowed to create a function in the production environment then, I had to solve the issue in SQL Server (2000) itself. The concatenated string of quantities at each BOM level was looking something like ‘1*1*2*4*1’ (here for the 5thBOM  level), so I needed a way to evaluate easily the respective product.

    In SQL Server the Cast and Convert functions allow to cast a string holding a numeric value to a numeric data type but their utility stops there.

-- Cast/Convert (working) example 
SELECT Cast('1' as int) * 25 
, Convert(int, '1') * 10 

 
    Many years ago, I remember I “tried my luck” and attempted to run a similar script like the below one:

-- Cast/Convert (not working) example 
SELECT Cast('1*2' as int) * 25 
, Convert(int, '1*2') * 10 

 
    No wonder that I got an error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1*2' to data type int.

   I attempted to find, again without luck, a built-in solution for my problem. For dynamic created scripts could be used, in general, the sp_executesql stored procedure:

-- expression evaluation using sp_executesql (output: 6) DECLARE @retval int; 
DECLARE @param int; 
SET @param = 3 
EXECUTE sp_executesql N'SELECT @retval = 1*2*@param', N'@param int, @retval int output', @param=@param, @retval = @retval output;  
SELECT @retval 

    Unfortunately the sp_executesql can’t be used from UDFs, therefore the functionality can’t be used in queries involving a select from a given object. However, given its simplicity, the string could be regarded as a list of numbers delimited by “*” (multiplication) sign, a loop or starting with SQL Server 2005, a common table expression could solve the problem but it will work only for this type of multiplications, unless the expression is parsed using specific mathematical algorithms. Then I wrote a function similar with the below one:

CREATE FUNCTION dbo.EvalProduct( 
@ListValues varchar(max)) 
RETURNS decimal(18,2) 
/* 
Purpose: evaluates the product of multiple numbers written as a text expression 
Parameters: @ListValues varchar(max) - List of values to be multiplied 
Sample call: SELECT dbo.EvalProduct('1*2*3*4') 
SELECT dbo.EvalProduct('1.2*2.3*3.4') 
SELECT dbo.EvalProduct('1.2') 
SELECT dbo.EvalProduct('NULL') 
*/ AS 
BEGIN 
DECLARE @Index int 
DECLARE @Product decimal(18,2) 
SET @Index = CharIndex('*', @ListValues) 
SET @Product = CASE 
     WHEN @Index>1 OR (@Index = 0 AND IsNumeric(@ListValues) = 1) THEN 1 
     ELSE NULL 
END  

WHILE @Index > 0  
BEGIN 
    SET @Product = @Product * Cast(Left(@ListValues, @Index-1) as decimal(18,2)) 

    SET @ListValues = Right(@ListValues, Len(@ListValues)-@Index) 
    SET @Index = CharIndex('*', @ListValues) 
END  

IF IsNumeric(@ListValues)=1 
SET @Product = @Product * Cast(@ListValues as decimal(18,2))  

RETURN @Product 
END 

          The function, even if handy, it has its limitations, first of all it works only with products of positive numeric values, and secondly there is no chance to use it for more complex expressions. Fortunately, starting with SQL Server 2005 there is an alternative.  In last post, Data Profiling Using CLR Functions I introduced ExecuteScalarToInt and ExecuteScalarToDecimal CLR functions which could be used to evaluate a text expression to an integer, respectively a decimal value, and thus they could be used also to solve the above problem:  

-- CLR functions approach (Output: 120, respectively 236.48) 
SELECT dbo.ExecuteScalarToInt('SELECT 1*2*3*4*5') IntExample 
, dbo.ExecuteScalarToDecimal('SELECT 1.2*2.3*3.4*4.5*5.6') DecimalExample  

    The functions could be actually used with any type of expression that returns a numeric value, the expressions could include also built-in or user-defined functions:
 

-- CLR with built-in mathematical functions (Output: 21.76)
SELECT dbo.ExecuteScalarToDecimal('SELECT Pi()*SQRT(47.98)') 

--CLR with UDF (Output: 9.38) 
SELECT dbo.ExecuteScalarToDecimal('SELECT dbo.EvalProduct(''1.2*2.3*3.4'')') 

    This means that the two functions could be used for example in queries in which the formulas following to be executed are stored in tables or built dynamically. Let’s look at an example based on the values stored in a table:

-- creating a simple table 
 CREATE TABLE dbo.TestTextExpressions ( 
Expression varchar(max)) 

--inserting the values 
INSERT INTO dbo.TestTextExpressions 
VALUES ('1*2*3*4') 
, ('Pi()*SQRT(47.98)') 
, ('Cos(10)') 
, ('Year(GetDate())') 
, ('Square(10.45)') 

-- running the formulas 
SELECT Expression 
, dbo.ExecuteScalarToDecimal('SELECT ' + Expression) [Output]      
FROM dbo.TestTextExpressions   

 
   
Notes:
1.    Instead of adding the SELECT in the parameter string, it could be created a CLR function (e.g. EvalToInt) that includes it already. Not sure how much benefit could be obtained from this, however I prefer to have generalized functionality rather then providing specific functions for each scenario.
2.    Most probably there are also exceptions in which this approach can’t be used. As the two CLR functions don’t perform any validation, the usefulness of such functions depends also on the validity of the input. If the input is invalid, then an error will be thrown; on the other side, even if it’s possible to catch the error in the CLR functions itself, it could prove to be difficult to alter function’s behavior (for example returning a NULL value) without impacting the result.
3.    In order to run UDFs from CLR UDFs, the SystemDataAccess:=SystemDataAccessKind.Read must be added in CLR functions definitions, otherwise “This statement has attempted to access data whose access is restricted by the assembly” error message will be thrown.

PS:  It would be nice to receive some feedback on how the above functions helped you to solve a similar problem!