Featured Posts

"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


Recent Posts