"Herb SQL" Easy way to generate statements and debug them.
There are many times when I have found it necessary to perform simular operations to large numbers of objects. Applying privileges, performing calculations, creating views, recovering databases the list goes on and on. Over the years many features have emerged to assist with this process. Each new approach comes with its own set of opportunities. Many emerging tools are industrial strengthed and prove to be the best practice for the tasks that they are designed to perform. Some of these types of tools are Wizards and Templates.
Many times I find an additional need to perform very specific tasks that are unique to the problem at hand. Maybe there is a tool that does most of the task but requires a few key strokes or additional lines of code in order to finish the job. That is great until the number of repetitions gets bigger. It may only take a few seconds to add a few key strokes but to add a set of key strokes to fifty scripts starts to become tedious. Often times the number may become hundreds of scripts. Then you do it again because something else changed.
Any of these scenarios might be described as performing a set of operations to or for a set of objects. The set of operations to be performed by also be comprised of other subsets of operations for each object and any of the operations to be performed may be conditional.
You have probably solved these problems many times.
As soon as I realized that the word "set" was found in my perspective of the matter at hand my mind went to SQL might provide a consistent solution. Once I realized that, I was able to generate a lot of work fairly quickly by using SQL. The problem then emerged that I did too much with SQL, or at least too much for me. The more that I generated, the more complex my generation statements became. Sometimes my productivity was lost because of the errors that I introduced and the difficulty in discovering the cause.
It occurred to me that the bigger and more complex my generations were, the more error prone they became and the more the return on my investment of effort diminished. I had already learned this phenomenon regarding many other disciplines but I ignored this problem when generating SQL statements.
It eventually became apparent that I could consistently generate small pieces of code that were easy to debug and put them together into a larger solution without losing the simplicity of debugging the small bits of code. All I had to do was to assign the individual bits of code that I wanted to generate a key. Each bit of information is for a particular purpose and that can be uniquely identified. In addition to the unique purpose, each bit of text may need to be in a particular position with respect to other pieces of text. If it may require a certain position then it would hold true that if I simply say that it "must" require placement in a particular position then the rule will still work. The key would have to uniquely identify the bit of parsing for that statement and express the position as a whole as well as the position in each part.
These were the basic problems and solutions that I had encountered. All I had to do was assign appropriate keys and have the ability to navigate the code generation code to the key that identified the source of the problem.
I will give an example of a problem that was solved using this approach. At this time I won’t go into the details of the step-by-step authoring process but just state the problem and show the solution.
The need in this example is to develop standard update procedure for a given table.
Try it out and see what you think!
Best regards
We will select the table HumanResources.Department in the AdventureWorks2008R2 database.
Step 1: Create Stored procedure generator (Herb SQL)
USE [AdventureWorks2008R2]
GO
/****** Object: StoredProcedure [dbo].[usp_Upd_Generator] Script Date: 1/19/2015 10:22:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_Upd_Generator]
@table nvarchar(255),
@schema nvarchar(255),
@debug bit = 0
AS
SELECT
case when @debug = 0 then the_data else the_key + '| ' + the_data end as [Statement]
FROM
(
--DROP STORED PROC IF IT EXISTS
SELECT RTRIM(sysobjects.[name]) + ':0000:' + '0000' the_key,
'IF OBJECT_ID(' + '''' + '[' + rtrim(SCHEMA_NAME(schema_id)) + '].'
+ '[usp_Upd_' + RTRIM(sysobjects.[name]) + ']' + '''' + ','
+ '''' + 'P' + '''' + ')' + ' IS NOT NULL'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
UNION
SELECT RTRIM(sysobjects.[name]) + ':0000:' + '0001' the_key,
'DROP PROCEDURE' + ' ' + '[' + rtrim(SCHEMA_NAME(schema_id)) + '].' + '[usp_Upd_' + RTRIM(sysobjects.[name]) + ']'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
UNION
SELECT RTRIM(sysobjects.[name]) + ':0000:' + '0002' the_key,
'GO'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
UNION
--BEGIN CREATE STATEMENT
SELECT RTRIM(sysobjects.[name]) + ':0010:' + '0000' the_key,
'CREATE PROCEDURE ' + '[' + rtrim(SCHEMA_NAME(schema_id)) + '].'+ '[' + 'usp_Upd_' + RTRIM(sysobjects.[name]) + ']'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
UNION
SELECT RTRIM(sysobjects.[name]) + ':0030:' + RIGHT('000'+CAST(syscolumns.colorder AS VARCHAR(4)),4) the_key,
CASE WHEN syscolumns.colorder = (1)
THEN ' ' ELSE ','
END + '@' +
syscolumns.[name]
+ ' ' + systypes.NAME /*+ ' '*/ +
CASE systypes.NAME
WHEN 'nvarchar' THEN '(' + RTRIM(CAST(syscolumns.length/ 2 AS CHAR)) + ')'
WHEN 'nchar' THEN '(' + RTRIM(CAST(syscolumns.length/ 2 AS CHAR)) + ')'
WHEN 'varchar' THEN '(' + RTRIM(CAST(syscolumns.length AS CHAR)) + ')'
WHEN 'char' THEN '(' + RTRIM(CAST(syscolumns.length AS CHAR)) + ')'
WHEN 'int' THEN ' '
ELSE ' '
END
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
AND systypes.NAME <> 'sysname'
and systypes.xtype = systypes.xusertype
UNION
SELECT RTRIM(sysobjects.[name]) + ':0100:' + '0000' the_key,
'AS'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
UNION
-- BEGIN UPDATE STATEMENT
SELECT RTRIM(sysobjects.[name]) + ':0300:' + '0000' the_key,
'UPDATE '+ '[' + rtrim(SCHEMA_NAME(schema_id)) + '].' + '[' + RTRIM(sysobjects.[name]) + ']'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
UNION
--SET VALUES FOR COLUMNS
SELECT RTRIM(sysobjects.[name]) + ':0500:' +
RIGHT('0000'+CAST(syscolumns.colorder AS VARCHAR(4)),4) the_key,
CASE WHEN syscolumns.colorder =
(
SELECT MIN(syscolumns.colorder)
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and syscolumns.[name] not in
(select kcu.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
and kcu.TABLE_NAME = tc.TABLE_NAME
where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY' )
and kcu.TABLE_NAME = @table
)
)
THEN 'SET ' ELSE ', ' END + syscolumns.[name] + ' ' + '= ' + '@' + syscolumns.[name]
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
AND
syscolumns.[name] not in
(select kcu.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
and kcu.TABLE_NAME = tc.TABLE_NAME
where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY' )
and kcu.TABLE_NAME = @table)
and syscolumns.[name] not in('DateStamp','ModifiedByID')
UNION
select kcu.TABLE_NAME + ':0600:' + RIGHT('000'+CAST(kcu.ORDINAL_POSITION AS VARCHAR(4)),4) as the_key
,
case kcu.ORDINAL_POSITION when 1 then 'Where ' else 'and ' end
+ rtrim(kcu.COLUMN_NAME) + ' = @' + rtrim(kcu.COLUMN_NAME)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
and kcu.TABLE_NAME = tc.TABLE_NAME
where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY' )
and kcu.TABLE_NAME = @table
and tc.CONSTRAINT_SCHEMA = @schema
UNION
SELECT RTRIM(sysobjects.[name]) + ':9000:' + '0000' the_key,
'GO'
AS the_data
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
INNER JOIN systypes ON systypes.[xtype] = syscolumns.[xtype]
INNER JOIN sys.tables ON sysobjects.[id] = sys.tables.[object_id]
WHERE sysobjects.[type] = 'u'
AND sysobjects.[name] = @table
and rtrim(SCHEMA_NAME(schema_id)) = @schema
)
AS my_intermediate_result
Step 2: Run the stored Proceure.
USE [AdventureWorks2008R2]
GO
DECLARE @return_value int
EXEC [dbo].[usp_Upd_Generator] @table = N'Department'
,@schema = N'HumanResources',@debug=0
SELECT 'Return Value' = @return_value
Step 3: Obtain the following Output from the stored Procedure and run it.
IF OBJECT_ID('[HumanResources].[usp_Upd_Department]','P') IS NOT NULL
DROP PROCEDURE [HumanResources].[usp_Upd_Department]
GO
CREATE PROCEDURE [usp_Upd_Department]
@DepartmentID smallint
,@Name AccountNumber
,@Name Name
,@Name nvarchar(50)
,@Name OrderNumber
,@Name Phone
,@GroupName AccountNumber
,@GroupName Name
,@GroupName nvarchar(50)
,@GroupName OrderNumber
,@GroupName Phone
,@ModifiedDate datetime
AS
UPDATE [HumanResources].[Department]
SET Name = @Name
, GroupName = @GroupName
, ModifiedDate = @ModifiedDate
Where DepartmentID = @DepartmentID
Step 4: Run a test.
USE [AdventureWorks2008R2]
GO
select * from humanresources.Department where departmentid = 1
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_Upd_Department]
@DepartmentID = 1,
@Name = N'Engineering',
@GroupName = N'Research and Development',
@ModifiedDate = N'2015-06-01 00:00:00.000'
SELECT 'Return Value' = @return_value
GO
select * from humanresources.Department where departmentid = 1
go
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_Upd_Department]
@DepartmentID = 1,
@Name = N'Engineering',
@GroupName = N'Research and Development',
@ModifiedDate = N'2002-06-01 00:00:00.000'
SELECT 'Return Value' = @return_value
GO
select * from humanresources.Department where departmentid = 1
Contact me at: allaboutthedata@comcast.net
