More code and slide from Code Camp 2009.2 coming

Until you put together one of these presentations you just don’t know how much time it takes.

In the near future I will be publishing all of the code and the slides from the Code Camp 2009.2 presentation titled: Advanced Workflow Processes in T-SQL.  I have the code and it works.  What’s missing is a deployment script and a a guide as to what's there and how to use it.

I promise to post the code as soon as I get a deployment script.  Samples of using the code and a guide will come latter.

The Life & Times of a SQL Temp Table

Code from Philly.NET code camp 2009.2 Saturday October 17, 2009

The first code block illustrates the scope of a temp table within dynamic T-SQL execution.  In short if you create a temp table any dynamic SQL executions within the same connection are in scope the scope of a temp table.

It also is a simple demonstration of the connection independence of temp tables.  Open two windows in SSMS connected to the same database.  Paste this code in both windows and execute multiple times each. 

Things to Notice:

• The Object_ID values in each window is always different
• If the final select statement is executed over and over it will always have the same values
  • Try Executing one window then the other and coming back to the first to just execute the final select.

--Duplicate content and run in another window

IF OBJECT_ID('tempdb..#MyTemp') IS NOT NULL 
    DROP TABLE #MyTemp


CREATE TABLE #MyTemp (
    SomeValue    INT NOT NULL
)

DECLARE @StartingNumber    INT


SELECT @StartingNumber = CAST(RAND()*1000 AS INT)


INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 1 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 2 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + @StartingNumber )


-- This would work if it were another module like a sproc or a udf
-- even nested calls
EXEC (N'INSERT INTO #MyTemp ( SomeValue )VALUES ( 200 );
INSERT INTO #MyTemp ( SomeValue )VALUES ( 12356 );')


SELECT 
      * 
    , OBJECT_ID('tempdb..#MyTemp') AS [#MyTemp OBJECT_ID]
FROM 
    #MyTemp AS mt

Alas the following code does not work. This example demonstrates that a temp table created in a dynamically executed statement is not in scope of the code that called the dynamic code.  The @SQLStatement variable contains the exact code of the previous example with the single tic (‘) escaped.  The select at the bottom shows that the table does not exist. If you should run it and it does have a result it is possible you did what I did in the presentation and executed in the same window as the previous example without dropping the temp table first.  If this should happen just drop the table and try again.  It really does work at showing that this technique does not work.

-------------------------------------------------------------------------------
-- Sadly this does not work
-------------------------------------------------------------------------------
-- Same code as above but executed dynamically
-- Tem table only exists during EXEC call
DECLARE @SQLStatement    VARCHAR(max)

SET @SQLStatement = '
IF OBJECT_ID(''tempdb..#MyTemp'') IS NOT NULL 
    DROP TABLE #MyTemp


CREATE TABLE #MyTemp (
    SomeValue    INT NOT NULL
)

DECLARE @StartingNumber    INT


SELECT @StartingNumber = CAST(RAND()*1000 AS INT)


INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 1 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 2 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + @StartingNumber )


-- This would work if it were another module like a sproc or a udf
-- even nested calls
EXEC (N''INSERT INTO #MyTemp ( SomeValue )VALUES ( 200 );
INSERT INTO #MyTemp ( SomeValue )VALUES ( 12356 );'')


SELECT 
      * 
    , OBJECT_ID(''tempdb..#MyTemp'') AS [#MyTemp OBJECT_ID]
FROM 
    #MyTemp AS mt

'

EXEC (@SQLStatement)

SELECT OBJECT_ID('tempdb..#MyTemp') AS [#MyTemp OBJECT_ID]

Exec vs sp_execute SQL

Code from Philly.NET code camp 2009.2 Saturday October 17, 2009

There are two large code blocks so don’t forget to scroll down.  The only reason for two is the JavaScript code formatter choked when it was too long.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

    -------------------------------------------------------------------------------
    -- Identical functionality executing a string
    -------------------------------------------------------------------------------
    -- Exec and Execute are interchangeable
    EXEC    (N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109')
    EXECUTE (N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109')

    EXECUTE sp_executesql N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109'

    -- Try removing the ( ) in the 1st statement - It doesn't work
    -- EXECUTE N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109'


    -------------------------------------------------------------------------------
    -- Not Identical functionality executing a string
    -------------------------------------------------------------------------------
    --Execute supports building the sting dynamically as part of the execution

    EXECUTE (N'SELECT * FROM AdventureWorks.HumanResources.Employee ' 
    + 'WHERE ManagerID = 109')

    EXECUTE sp_executesql N'SELECT * FROM AdventureWorks.HumanResources.Employee ' 
    + 'WHERE ManagerID = 109'
    -- This doesn't work because the text string is an invalid as a parameter to a SP


GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @SelectSQL        VARCHAR(Max)
DECLARE @WhereSQL        VARCHAR(Max)
DECLARE @SQLStatement    NVARCHAR(Max) -- required by EXECUTE sp_executesql 
DECLARE @ManagerID        TINYINT 

SET @ManagerID = 109

SET @SelectSQL = 'SELECT * FROM AdventureWorks.HumanResources.Employee ' --<<---Notice Space at end
SET @WhereSQL =  'WHERE ManagerID = ' + CAST(@ManagerID AS VARCHAR(10))
SET @SQLStatement = @SelectSQL + @WhereSQL

SELECT @SelectSQL, @WhereSQL, @ManagerID, @SQLStatement


EXEC (@SelectSQL + @WhereSQL) -- Must use or () or the command is a totally different thing
EXEC (@SQLStatement)

EXECUTE sp_executesql @SQLStatement

-- These don't work
--EXEC @SelectSQL + @WhereSQL -- Must use or () or the command is a totally different thing
--EXEC @SQLStatement

-- These don't work either since ( ) not allowed in Sproc execution
--EXECUTE sp_executesql (@SelectSQL + @WhereSQL)
--EXECUTE sp_executesql (@SQLStatement)



GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically with parameter
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXECUTE sp_executesql 
    N'SELECT * FROM AdventureWorks.HumanResources.Employee 
    WHERE ManagerID = @ManagerID',
    N'@ManagerID tinyint',
    @ManagerID = 109;

-- Use of explicitly named parameters
-- Below the line is dynamic in number of parameters accepted
EXECUTE sp_executesql 
      @stmt = N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = @ManagerID'
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = 109; -- Good to use ;


GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string Variable dynamically with parameter
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @SelectSQL        VARCHAR(Max)
DECLARE @WhereSQL        VARCHAR(Max)
DECLARE @SQLStatement    NVARCHAR(Max) -- required by EXECUTE sp_executesql 
DECLARE @ManagerID        TINYINT 

SET @ManagerID = 109

SET @SelectSQL = 'SELECT * FROM AdventureWorks.HumanResources.Employee ' --<<---Notice Space at end
SET @WhereSQL =  'WHERE ManagerID = @ManagerID' --<<-- Notice use of Variable in string
SET @SQLStatement = @SelectSQL + @WhereSQL

SELECT @SelectSQL, @WhereSQL, @ManagerID, @SQLStatement


EXECUTE sp_executesql 
      @stmt = @SQLStatement
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = @ManagerID; 
    

-- Change the value of the parameter and execute again
-- Execution plan cached and parameterized
SET @ManagerID = 108

EXECUTE sp_executesql 
      @stmt = @SQLStatement
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = @ManagerID; 
    
    

GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Parameterized SQL with output variables
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @ManagerID        TINYINT 
DECLARE @NumRecs        INT

SET @ManagerID = 109


EXECUTE sys.sp_executesql 
      @stmt = N'SELECT @Count = count(*) FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = @ManagerID'
    , @params = N'
          @Count    int output -- output need here too
        , @ManagerID    tinyint
        '
    , @Count    = @NumRecs OUTPUT  -- Don't for get output
    , @ManagerID = @ManagerID
    ;    

SELECT @NumRecs AS [@NumRecs], @ManagerID AS [@ManagerID]

-- did you notice the comment in the @params section

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Interesting variations
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- sp_tables [ [ @table_name = ] 'name' ] 
--    [ , [ @table_owner = ] 'owner' ] 
--    [ , [ @table_qualifier = ] 'qualifier' ] 
--    [ , [ @table_type = ] "type" ] 
--    [ , [@fUsePattern = ] 'fUsePattern'];
 
---------------------------------------
-- No Params since they are all optional
---------------------------------------
EXEC sys.sp_tables
;

---------------------------------------
-- Position aligned params
---------------------------------------
EXEC sys.sp_tables NULL,NULL,NULL,NULL,NULL
;
EXEC sys.sp_tables NULL,'HumanResources',NULL,NULL,NULL
;


---------------------------------------
-- named params
---------------------------------------
EXEC sys.sp_tables       
      @table_name = NULL
    , @table_owner = NULL
    , @table_qualifier = NULL
    , @table_type = NULL
    , @fUsePattern = NULL 
;

EXEC sys.sp_tables
      @table_name = DEFAULT --<<-- Notice use of default
    , @table_owner = 'Sales'
    , @table_qualifier = DEFAULT
    , @table_type = "'VIEW','TABLE'"
    , @fUsePattern = DEFAULT 
;

---------------------------------------
-- Can change the order of named params
---------------------------------------
EXEC sys.sp_tables
      @table_owner = 'Sales'
    , @table_type = "'VIEW'"
    , @table_qualifier = NULL
    , @fUsePattern = NULL 
    , @table_name = DEFAULT
;    

---------------------------------------
-- can omit params with a default val
---------------------------------------
EXEC sys.sp_tables
      @table_type = "'VIEW'"
    , @table_owner = 'Sales'
;


go

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- And now for something compeletely Different
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

DECLARE @SprocName    VARCHAR(max)
DECLARE @Owner        VARCHAR(64)

SET @SprocName  = 'sys.sp_tables'
SET @Owner        = 'Sales'

EXEC @SprocName                    --<<-- Notice the lack of ( ) 
      @table_owner = @Owner
    , @table_type = "'VIEW'"

-- This is an example of dynmically executing a stored procedure with Exec syntax

Philly.net Code Camp 2009.2 Saturday October 17, 2009

 

 

Well the 2nd code camp of the year was another big success.  Lots of campers showed up from the largest registration yet, 750.

Thanks to all who showed up first thing in the morning and attended my SQL presentation, Advanced Workflow Processes in T-SQL.  This was my fourth or fifth time presenting at code camp so there were no worries about being a presenter.  However,  this was the first presentation of this kind or should I say magnitude for me. 

Usually, the presentations are on very specific technical aspects of T-SQL such as XML, High Performance Set based Techniques, New Features of SQL 2008.  They tend to be composed of many smaller independent sections.  It would be easy to alter the presentation to a smaller time slot but knocking out a section or two.

The Advanced Workflow Processes in T-SQL presentation contained large conceptual sections combined with technical demonstrations of  T-SQL syntax and followed by a real example.  The goal was to spark new ideas and new ways of thinking about T-SQL software development.

The feedback from attendees was overwhelmingly positive and astoundingly enthusiastic. People were finding me all day to let me know how much they took from it and how they were planning on using the techniques right a way.  As a presenter this is a very gratifying outcome.

One gentleman let me know he was planning on integrating it directly into an existing SSIS package.  Instead of handling all of the logic in SSIS he was just going to have a step that called a stored procedure with a parameter.  All the customizations and variations needed for the different modes of operation were just going to be handled in the stored procedure.  The sproc called from SSIS would look up, in a new table, the custom sproc to execute based on the input and dynamically execute it.  He was thrilled by the idea of never having to touch the SSIS package again to implement new or make modifications to an existing variation of the logic. Cleary the message of the presentation was transmitted.

There were some criticism of the presentation that were also useful.  Believe it or not there are two other demos that were slated for this presentation showing alternate techniques for dealing with the issues and uses of dynamic T-SQL workflows.  If you attended the 1 hour and 20 minute presentation you clearly could see that there is just no place for more.  This leads to one of the most useful criticism, which is paraphrased as this: “There were some really interesting an novel bits of code that we didn’t get to explore enough.  We could have used less of the theory and more of the code.”

I agree with the criticism in hindsight.  However, based on the feedback from the last few presentations there had been a request for deeper and less basic subject matter.  I still believe that there is an important need to study and comprehend the theory, best practices, and conceptual ideas behind software development as it applies to T-SQL.  However,  I need to find another way to address these conceptual pieces. 

The plan, at the moment, for the presentation is to break it up into a series. 

Dynamic T-SQL Execution – detailed look look at the techniques, syntax and quirks of using the EXECUTE command and the sp_executesql stored procedure.

The Magical World of The Temp Table and Other Mythical Temporary Persistence Objects – a deep look at the life and times of temp tables and table variables. A compare and contrast exercise between temp tables and table variables with an added focus on the new table variable parameters in SQL 2008.

The Fine Art of Stored Procedures & User Defined Functions – focusing on best practices and techniques of working with stored procedures.  Specific topics to include:

• Compare & Contrast Sproc vs. UDF
• Return Values vs. Output Parameters
• Parameters
• Default Values and how to ensure them
• Input validation
• Error handling
• Temp table & Cursor best practices.
• UDF types & Limitations
• Capturing table set output of a sproc
• What breaks when you have a linked server involved

Dynamic T-SQL Workflows – This presentation will assume that all the previous presentation topics are well understood. The focus will be squarely on applying all the techniques to enable dynamic, flexible, and easy to modify or variegate work flows in T-SQL.

Data Quality Status Monitoring – A Dynamic T-SQL Workflow case study – The use of the dynamic T-SQL workflow techniques applied to the monitoring of the quality of data in a database. The use of this technique is beneficial in development and production environment.  A basic illustration of the workflow as well as details on specific areas in a db where DRI & Constraints don’t work but should be monitored.

T-SQL Delta Engine for determining changes between two snapshots of data – A Dynamic T-SQL Workflow case study – Say you have two snapshots of data and you need to know what's been added, dropped, or modified between them.  This presentation is a case study of the use of a dynamic T-SQL workflow process to enable custom rules to be applied to a data structure to determine the differences between two sets of data.

Customizable T-SQL ETL Engine – A Dynamic T-SQL Workflow case study – Extraction – Translation – And Loading of data is a bread and butter kind of activity for database developers.  If the source and target of an ETL application is constant then nobody really cares how its coded so long as it works and doesn’t take too long to run.  In reality though how it is implemented becomes very important as soon as it needs to be modified or have a variation created.  This presentation shows how to apply the techniques of dynamic T-SQL workflows to building robust, resilient, and flexible ETL process in T-SQL.

Other Topics

Just for fun while I was creating this list I cam up with a few off topic subjects I would also like to cover.  Mostly so I don’t forget but I would be thrilled for your input.

SQL Synonyms or In Other Words – A small brief on the power and use of the synonym.

Developer Productivity: SSMS Templates – How to create and use templates in SQL Server Management Studio.  If I learn how I would also include SQL Command Mode too.

Developer Productivity: Tools for SSMS – Demonstrations of some of the built-in and 3rd party tools to enhance developer productivity in Server Management Studio.

Split full db object names into parts with a little help from a friend

so you have a string in T-SQL such as:

MyProdSvr.ReallyGoodDB.SomeSchema.MyFavoriteTable

Woldn't it be nice if there was an easy mecanism to crack that code.

Try This:

DECLARE @MyObjectFullName	sysname

SET @MyObjectFullName = 'MyProdSvr.ReallyGoodDB.SomeSchema.MyFavoriteTable'

SELECT
	  PARSENAME(@MyObjectFullName, 4) AS ServerName
	, PARSENAME(@MyObjectFullName, 3) AS dbName
	, PARSENAME(@MyObjectFullName, 2) AS SchemaName
	, PARSENAME(@MyObjectFullName, 1) AS ObjectName

ServerName	dbName	SchemaName	ObjectName
MyProdSvr	ReallyGoodDB	SomeSchema	MyFavoriteTable

{Thanks Chuck}