💎SQL Reloaded: Self-Join in Update Query II

    In yesterday’s post on the same topic I tried to exemplify how a self-join update query could be written in order to avoid an error raised by the database engine. Even if this type of query is normally written for hierarchical structures consisting of one-level parent-child relations stored in the same table, my example was using a simple join based directly on the primary key. During the day I was thinking that might be straightforward to create a simple hierarchical structure based on a relatively realistic example. For this let’s consider a department in which the performance of the the manager is a function of the performance of its employees, while the number of hours of training allocated to each employee is proportional to the number of hours allocated to its manager. For this let’s consider the following table:  

--Employee table's script 
CREATE TABLE [dbo].[Employees]( 
[EmployeeID] [int]  NOT NULL 
, [ManagerID] [int]  NULL 
, [Name] [nvarchar] (50) NULL 
, [Performance] [numeric]  (5, 3) NULL 
, [TrainingHours] [numeric]  (5, 2) NULL ) 
ON [PRIMARY] 

-- inserting the test data 
INSERT INTO dbo.Employees 
VALUES (1, NULL, 'Joe', NULL, 10) 
, (2, 1, 'Jack', .65, NULL) 
, (3, 1, 'Mary', .45, NULL) 
, (4, 1, 'Ross', .54, NULL) 
, (5, 1, 'Scott', .48, NULL) 
, (6, NULL, 'Jane', NULL, 15) 
, (7, 6, 'Sam', .50, NULL) 
, (8, 6, 'Ron', .45, NULL) 

   In order to calculate Manager’s performance we could use a query similar with the following, of course the formula could be more complicated and not a simple average:  

-- updating Manager's Performance 
UPDATE dbo.Employees 
SET Performance = EMP.Performance 
FROM ( -- inline view 
     SELECT ManagerID 
     , AVG(Performance) Performance 
     FROM dbo.Employees 
     WHERE ManagerID IS NOT NULL 
     GROUP BY ManagerID 
) EMP 
WHERE dbo.Employees.ManagerID IS NULL 
AND dbo.Employees.EmployeeID = EMP.ManagerID 

    Let’s check the updates and their correctitude: 

-- Checking updated data 
SELECT * 
FROM dbo.Employees 
WHERE ManagerID IS NULL -- Verifying output SELECT ManagerID 
, AVG(Performance) Performance 
FROM dbo.Employees 
WHERE ManagerID IS NOT NULL 
GROUP BY ManagerID       

      

Note:     
    The average needs to be calculated in the inline view. In case are retrieved more records for each record from the updated table, the query will still work though the result is “unpredictable”: 

-- updating Manager's Performance w/o aggregates 
UPDATE dbo.Employees 
SET Performance = EMP.Performance 
FROM ( -- inline view 
     SELECT ManagerID 
     , Performance 
     FROM dbo.Employees 
     WHERE ManagerID IS NOT NULL 
) EMP WHERE dbo.Employees.ManagerID IS NULL 
AND dbo.Employees.EmployeeID = EMP.ManagerID       

          The reverse update based on the number of hours of training could be written as follows:  

-- updating Employees' Training Hours 
UPDATE dbo.Employees 
SET TrainingHours = 0.75 * EMP.TrainingHours 
FROM ( -- inline view 
    SELECT EmployeeID 
    , TrainingHours 
    FROM dbo.Employees 
   WHERE ManagerID IS NULL 
) EMP 
WHERE dbo.Employees.ManagerID IS NOT NULL 
AND dbo.Employees.ManagerID = EMP.EmployeeID       
      
-- Checking updated data 
SELECT * 
FROM dbo.Employees 
WHERE ManagerID IS NOT NULL -- Verifying output SELECT EmployeeID 
, 0.75 * TrainingHours TrainingHours 
FROM dbo.Employees 
WHERE ManagerID IS NULL   

        

💎SQL Reloaded: Self-Join in Update Query I

    While reading R. Scheldon’s article on “UPDATE Basics in SQL Server” I remembered about a problem I had long time ago while attempting to do a self-join in an Update query. Such a query is quite useful in hierarchical structures consisting of one-level parent-child relations stored in the same table, when needed to update the parent based on child information, or vice-versa. The problem I had could be also exemplified by using a simple table (no hierarchical structure), here’s the query:  

-- self-join update - problematic query 
UPDATE Production.Product 
SET StandardCost = ITM.StandardCost* (1+.012) 
FROM Production.Product ITM 
WHERE Production.Product.ProductID = ITM.ProductID 

 The above statement returns the following error:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Production.Product.ProductID" could not be bound.

    In order to avoid this error message, the table from the FROM clause could be included in an inline view, something like:

-- self-join update - solution 
UPDATE Production.Product 
SET StandardCost = ITM.StandardCost*(1+.012) 
FROM ( -- inline view 
    SELECT * 
    FROM Production.Product ITM 
    ) ITM  
WHERE Production.Product.ProductID = ITM.ProductID 

    The inline view could be replaced with a standard view, table-valued UDF or the update could be done through a view. For exemplification I will use only the first and third case, here’s the view based on Production.Product table and the queries corresponding to the two cases:

-- view based on Production.Product 
CREATE VIEW Production.vProductTest 
AS 
SELECT *  
FROM Production.Product   

-- self-join update through view update 
UPDATE Production.vProductTest 
SET StandardCost = ITM.StandardCost* (1+.012) 
FROM Production.Product ITM 
WHERE Production.vProductTest.ProductID = ITM.ProductID     

-- self-join update from view 
UPDATE Production.Product 
SET StandardCost = ITM.StandardCost* (1+.012) 
FROM Production.vProductTest ITM 
WHERE Production.Product.ProductID = ITM.ProductID 

Note:
1.    The first update query was written in this way only to exemplify the self-update within an hierarchical structure, if it’s needed to modify the StandardCost and nothing more, then the update could be written simply in any of the following forms:

-- Query 1: update statement 
UPDATE Production.Product 
SET StandardCost = StandardCost* (1+.012) 

--Query 2: update statement 
UPDATE ITM 
SET StandardCost = StandardCost * (1+.012) 
FROM Production.Product ITM 

2.    Because the join constraint is based on a hierarchical structure that considers in the join different columns, the query can’t be written as follows:

 
--simple update statement 

UPDATE Production.Product 
SET StandardCost = ITM.StandardCost* (1+.012) 
FROM Production.Product ITM 

 
   This query is nothing more than an alternative to the queries shown in the first note.

💎SQL Reloaded: Porting 32 bit CLR UDFs on 64 bit Platforms

Today I tried to port on a 64 bit platform a few of the CLR UDFs created in the previous posts, this time being constrained to use Visual Basic Studio 2010 Express to create and build the assembly on a x86 platform, and install the assembly on a x64 SQL Server box. From the previous troubleshooting experience between the two platforms, I knew that there will be some challenges, fortunately there was nothing complex. Under SSIS 2008 it’s possible to choose the targeted platform, therefore I was expecting to have something similar also in VB Studio 2010 Express, and after a simple review of Project Properties, especially in what concerns the Compile settings, I found nothing relevant. I tried then the standard approach, so I built the solution, copied the .dll on the target server and tried to register the assembly though I got the following error:

Msg 6218: %s ASSEMBLY for assembly '%.*ls' failed because assembly '%.*ls' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message%.*ls

After several attempts to google for a solution on how to port 32 bit CLR UDFs on 64 bit Platform or on how to configure VB Studio 2010 Express in order to target solutions for 64 bit platforms, I found a similar question (VB Express target x86 Platform) in MSDN,  Johan Stenberg’s answer completed by JohnWein’s hint, led me to the “Issues When Using Microsoft Visual Studio 2005” document, to be more specific to 1.44 section "References to 32-bit COM components may not work in VB and C# Applications running on 64-bit platforms", of importance being the part talking about "Express Editions". In the document is specified how to modify the project and add in the first PropertyGroup section a PlatformTarget tag with the text value x86, therefore what I had to do was to add the respective tag but with the value x64. After doing this change everything worked smoothly. It’s kind of a mystery why Microsoft hasn’t enabled this feature in Express versions, but in the end I can live with it as long there is a workaround for it.

Happy coding!

#️⃣Software Engineering: Programming (Part III: Football and Software Development)

 

Software Engineering Series

I wanted to write this post during the South Africa World Cup 2010 though because of the lack of time and because I was waiting for some statistics I could use, here I am, two weeks after the final whistle of the game for the first place. Football and Software Development, two domains that seems to have nothing in common, even if many software developers like to play football, and many football players are spending lot of time in front of their laptops. There is actually an important coordinate in what concerns the two – team work. Of course, that’s common to many other sports, though there are some characteristics important mainly to soccer -the small rate of deliverables (goals), the rate of failures (wrong passes), and I bet there are other characteristics common to most of the team sports, like the division and specialization of work, migration of players, “project”-oriented work, flow of money, etc.

Looking back at the games from this World Cup, we have to notice that, with a few exceptions, there wasn’t a big difference between the teams anymore, trend that could be seen during the last championships too, and there were no more individual players gaining one game after the other. Nowadays it primes the collective work, the cohesion of a team, the way the players respect the tactical indications given to them, the way they communicate and feel each other on the field. It didn’t matter anymore that you were playing against a Ronaldo, a Messi, Lampard, Drogba or Rooney, small teams like Australia, Chile, New Zeeland or South Korea, fighting as equal against the favorite teams of this tournament. 

What is more important to notice, is that teams whose players cost and make millions, didn’t function well (as expected) because the team haven’t played as a team, because the sense of individuality primed, because there was no adequate communication inside the team, while the trainer didn’t knew how to make himself respected, how to select his team, how to make/take the best out of his players, or how to change the tactics to counteract the one of the adversary. So the team with the best paid/skilled players, the team which puts more effort or controls the game, the team which has the most dynamic, effective (from the number of goals), beautiful or pragmatic play doesn’t necessarily win the game, same as the best trainer can make mistakes too, can make himself easier misunderstood or become overnight a persona non grata for its team or public.

 The same observations could be applied also to software development, and with the risk of being criticized I would say that the team with the best developers/professionals does not necessarily make a project successful, especially when the sense of individuality primes for the one of team, when the team members don’t play as a team, when there is no adequate communication, when the managers doesn’t make himself respected and know how to make/take the best out of his players, how to make a team successful no matter of the team’s number and skill-set. I tend to believe that in software development, same as in football, it must matter the joy for playing in a team, the joy for playing, being an example of professionalism, collaborating in achieving the purpose, helping each other to become better, no matter if one is named player, trainer, masseur, doctor or federation member.

I think that trainers have to learn more about project management, given the fact that building and leading a competitive team has a lot to do with projects and project management, being driven by similar goals, objectives, scope, etc. On the other side I feel that managers have to learn more from the behavior and knowledge of a trainer, to know how to be authoritarian and when to be a friend. And I expect that there are many other aspects the two types of professionals share. 

Also IT professionals can learn from football players, especially in what concerns the team spirit, what it takes to be/become a team, what it means to have your place in the field, do it right and for the best of the team. Of course, the self-sacrifice must not be brought to extreme, as some players do. In what concerns the football players, they could learn from developers the simplicity, abnegation for their work, the abnegation of becoming better, of learning something new, of finding and knowing their place in life, and overall of being humble.

As for the executives dealing with IT projects they must learn that a defender can’t become overnight a goal-getter or a goalkeeper, that a new comer in the team needs time to adapt himself, and must be helped to become integrant part, that the new comer needs to find its pace and place in the team. Executives must learn that it takes time, effort and a good strategy to built good successful teams, and even if everything revolves around money (although it shouldn’t), there should be kept a balance between investments, effort and rewards, some continuity, respect and support toward achieving successful and competitive teams.

Note:
If you liked/found interesting this post, then you might be interested also in Yohasna’s post What can we learn form SPAIN's World Cup Victory in the world of Software? and my answer to it, Satya’s Football and Software teams.. How different are they?, or B. Dwolatzky’s article on Football and Software Development are both team sports.

Previous Post <<||>>  Next Post

💎SQL Reloaded: Some Notes on Dates

   The Date is one of the most difficult data types to handle, given the fact that it might take different formats (e.g. DD/MM/YYYY, MM/DD/YYYY, DD/MM/YY, DD-MON-YYYY, etc.), and even if the various formats make a limited list, the fact that each database, machine or application could work with a different date format or set of date formats, this makes developers’ life a nightmare. The representational complexity resides also in the fact that each country adopts one or more standard date formats, a mixture between day, month, year and the various delimiters used (e.g. “.”, “/”, “-”). For example the SQL Server Books Online lists several country-specific date formats that could be used with Convert function, while in Date and Time Formats tutorial could be found a list with the formats and literals used in Oracle. Even .

     The various frameworks have attempted to introduce modalities of handling dates (e.g. culture info), though none of them is bulletproof given the interaction/integration between the various layers. Sometimes I prefer to store the date as a (standard) text string and transmit it thus between layers, following to transform (convert) it as needed by the various data consumers (UI, components, databases, etc.). What format to choose is depends entirely on habitude, special requests/constraints or on the flexibility of converting a given format to other formats. A flexible approach is the ISO 8601 format that combines date and time representations (<date>T<time>) as YYYY-MM-DDThh:mi:ss.mmm (e.g. 2010-12-22T23:10:29.300). It’s important to note that in SQL Server this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings (see ISO 8601 Format), fact that makes the ISO format a good candidate for representing dates.

   The ISO format allows also to sort lexicographically a date, fact that makes it valuable when combining strings with dates, for example when naming files, though then some of the delimiters need to be removed. I prefer to remove all the delimiters thus storing a file with the timestamp in the format <meaningful_name><timestamp>.<extension> (e.g. ‘Report 20101222T231029.rpt’).

    When the time is not tracked, the date could be stored also as an integer in YYYYMMDD format, thus for example 2010-12-22 could be stored as 20101222. I’ve seen old databases making use of this technique, I found it awkward at the beginning though I discovered its performance benefits when doing comparisons, the difference of days between two dates reducing at a simple subtraction. Actually storing the date as integer is just a simplification of the way on how dates are stored, for example in SQL Server the date is stored as two integers,  in which the first integer stores the date itself and the second the time in clock ticks after midnight (see here). The danger comes when attempting to work with the integer format in order to express the difference between two dates in higher time units than the day (e.g. weeks, months, years), in such cases being recommended to work with built-in date functions. Given the fact that dates on SQL Server could be expressed also as float values (see Joe Celko’s article on Temporal Data Types in SQL Server), the same danger occurs when using the numeric values in calculations in the detriment of date functions.

    In ERP systems or other “activity-based” systems is useful to store a calendar together with other calendar specific information – whether it’s a working date, the period in which the date falls, the fiscal week, month, years, etc. If in OLTP systems such structures are used to store the time dimension, thus each time record being referenced directly, in OLAP systems there could be a mix between between direct referencing of calendar records or of storing directly the date, following in developers’ attributions to retrieve additional information about the date when needed. As it doesn’t make sense to argue in here the benefits of the two approaches, I limit myself to note that the integer value of the date could be used as a foreign key when the time component is not needed to be stored. By storing a date as integer or to a shorter date format  (e.g. YYYYMM, YYYYWW, YYYY) could be impacted applications’ flexibility. Now it depends, there are cases and cases, different problems requiring different solutions. Just think twice when choosing on how to store a date, think not only about the current requirements but also how the requirements could change given a change in the process/business dynamics!

🌡Performance Management: Alfred Thompson's “Over-Educated, Yet Under-Qualified?” [1] (Answer)

Performance Management Series

In schools the accent is on theory and algorithms, the small projects target the learning of a technology, their complexity and difficulties involved being quite small when compared with real life applications. Taking an application from design to production and later during support phase requires time and the mix of knowledge from different fields, thing quite difficult to do in a school project, while the structural context in an organization, the requirements and work in a team, is again quite different. Going above the basic features of a programming language takes time, it depends on the learning curve of the programming language and the capacity of the learner, on the complexity of the tasks approached and on the knowledge (made) available. 

I can’t say that schools can do much in this direction because it’s quite difficult to cover all the aspects in just 8-20 classes, in which the students are introduced into the concepts and some basic applications. What the schools could do in order to support their students is to provide the required infrastructure (mainly computers), bring the technologies and learning material up to date, direct gradually the focus from theory to applicability, and eventually support users getting some additional experience in organizations. It’s in students’ attribution to make most of the learning experience in schools, though often even if the want, need and infrastructure is there, fighting with the lack of time is quite hard.

One of the tough realities in IT is that it takes time to link the dots, and as you already highlighted, it takes about a year before a college/university graduate to become really productive. Now I have to say that this depends also on the organization’s culture/environment, on how it supports the learning process, how it helps the new comer to become part of the team and become productive. I’m saying that because I’ve seen companies doing minimum in this direction, just expecting the new comer to catch everything on the fly and be productive in a matter of weeks. 

Those working in IT for a longer time know that is not entirely possible, though there are also some exceptions. There are also organizations that train the new comers, introduce them into tasks evolving in complexity based on each person’s skills, provide resources (software tools, books, courses and other type of learning material) and an environment that facilitates learning. Having time allocated for learning new things, participating in activities that allow the distribution of knowledge within a team, having professionals whom you could ask questions or who could mentor you through the learning process, I consider all these as being essential for a modern IT organization.

The theory learned in schools need to be supported by hand-on experience in order to make most of the learning process, IT organizations are maybe the best places to do that, though I’m not sure how much that is possible. There are schools, organizations and governments that support this type of learning, though, unfortunately is not everywhere possible to do that or at least not for everybody. I think it’s in everybody’s interest to make most of the learning process, for schools to have highly skilled graduates, for organizations to have productive employees, a pool of college graduates resources from where they could select potential employees, for students to be skilled, and thus have higher chances of finding a job, while for governments this could lead in theory to a smaller unemployment rate. I find important the constructive involvement of all parties; now, I wonder how many schools, organizations or governments are trying to do something, change something into this direction.

References:
[1] Alfred Thompson (2010) “Over-Educated, Yet Under-Qualified?”

💎SQL Reloaded: Running a Statement for Each Database (CLR Version)

    I continue the series of posts on the use of the dbo.ExecuteScalarTo  CLR functions created in a previous post, this time, as is mentioned in the title, by running a statement for each database. The old-fashion method of a statement for each database is with the help of undocumented stored procedure sp_MSforeachdb, see for example ReplTalk’s post found recently. Actually the respective example based on the use of DatabaseProperty function has been given only for exemplification, the values available through the DatabaseProperty function could be retrieved directly from the sys.databases system table: 

 -- querying directly the values 
SELECT database_id 
, name DatabaseName 
, Is_Fulltext_enabled IsFulltextEnabled 
, Is_Ansi_Null_Default_on IsAnsiNullDefault 
, Is_Ansi_Nulls_on IsAnsiNullsEnabled 
, Is_Ansi_Warnings_on IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    Here’s the same query rewritten using the DatabaseProperty function:

 -- DatabaseProperty function at work 
SELECT database_id 
, name DatabaseName 
, DATABASEPROPERTY( name ,'IsFulltextEnabled') IsFulltextEnabled 
, DATABASEPROPERTY( name ,'IsAnsiNullDefault') IsAnsiNullDefault  
, DATABASEPROPERTY( name ,'IsAnsiNullsEnabled') IsAnsiNullsEnabled  
, DATABASEPROPERTY( name ,'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The same query could be rewritten using the dbo.ExecuteScalarToString function:  

 -- DatabaseProperty function within CLR function 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsFulltextEnabled'')') IsFulltextEnabled 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullDefault'')') IsAnsiNullDefault  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullsEnabled'')') IsAnsiNullsEnabled  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiWarningsEnabled'')') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The usefulness of the dbo.ExecuteScalarToString is minimal in this case, however it could be considered more “complex” examples. For example retrieving a count of the number of tables, views, assemblies or users existing in a database:

-- running a statement for each database 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.tables') NumberTables 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.views') NumberViews 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.assemblies') NumberAssemblies 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.sysusers') NumberTables 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

   Now it depends on each developer’s inspiration and on the problem attempting to solve. I find dbo.ExecuteScalarToString CLR function quite useful when attempting to run for each database multiple queries that returns a single value. On the other side sp_MSforeachdb provides more flexibility in what concerns the types of queries run, though specific techniques (e.g. temporary tables or table variables) need to be used in order to retrieve sp_MSforeachdb’s output in a single result-set (see for example the Locally Fragmented Indexes example from MSDN’s article on Uncover Hidden Data To Optimize Application Performance).

💎SQL Reloaded: Number of Records II (The DMV Approach)

    In SQL Server 2000 the safest way to return the total number of records from a table was to do a simple COUNT. I’m say the safest way because (for big tables) there was a faster but approximate way to obtain the same information by using sysindexes table, which, as its name denotes, was storing one record for each index and table in a given database. When I started to use SQL Server 2005 and further 2008 I naturally followed the same approach, from several reasons not attempting to find whether there is a better alternative. Yesterday, while scanning roughly the titles from MSDN blogs, my attention was caught by Martinjnh’s post SQL Server–HOW-TO: quickly retrieve accurate row count for table, in which, in addition to COUNT and sys.sysindexes view kept from compatibility reasons,  he gives 2 other alternatives that use two sys.partitions and sys.dm_db_partition_stats DMV (dynamic management views), that contain “a row for each partition of all the tables and most types of indexes in the database”, respectively the “page and row-count information for every partition in the current database”.

    Of course, I tested the queries and their accuracy by inserting/deleting a few records in one of the AdventureWorks tables. The “statistics” seems to be updated in real time, thing certified also by the SQL Server 2005 documentation. I wanted to see the definition of the respective views, though they are not browsable by using the Management Studio. Some time ago I discovered that the definition of system views, functions and stored procedures are available though the sys.all_sql_modules view, the union join between sys.sql_modules and sys.system_sql_modules views. Here’s the query I used:

-- retrieving the definition of system views used to return the number of records 
SELECT * 
FROM master.sys.all_sql_modules 
WHERE CHARINDEX ('sys.dm_db_partition_stats', definition)>0 
      OR CHARINDEX ('sys.sysindexes', definition)>0 
       OR CHARINDEX ('sys.partitions', definition)>0 

Notes:
1.   Another way to get the definition of an object is to use the Object_Definition function:

SELECT object_definition(object_id('sys.sysindexes')) 

2.   Following Mladen Prajdic’s post, an SQL Server MVP, I found out that all the system objects are stored in the (hidden) read-only MsSqlSystemResource database that complements the master database.

3.   Diving into the three view’s definition, can be seen that all of them use the OpenRowSet function in order to make calls to INDEPROP, PARTITIONCOUNTS, ALUCOUNT internal SQL Server tables. As it seems this functionality of OpenRowSet function can’t be (mis-)used by users.

4. I found several interesting posts on sys.dm_db_partition_stats, for example Cindy Gross’ post, a Support Engineer at Microsoft, the blog containing several scripts and links to more resources.

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