Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database.  So here is the SQL for that.

select min(DB) as DB, name, type

from

(

select 'ReleaseManagerTest' as DB, name, type 

from ReleaseManagerTest.sys.objects 

union all

select 'ReleaseManager' as DB, name, type

from ReleaseManager.sys.objects

) A

group by name, type

having count(*) <> 2

I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added.  So I created a quick stored proc that I now have in my tool box.

create proc obj(@name varchar(50)=null, @type char(1)=null)

as

begin

set nocount on

select o.name, 

o.type, 

convert(varchar(10),o.create_date,1) created, 

convert(varchar(10),o.modify_date,1) modified

from sys.objects o

where (@name is null or o.name like @name)

and (@type is null or o.type = @type)

and o.is_ms_shipped = 0

order by o.modify_date desc

end

go

So if I just do:
obj ‘get%’, ‘P’

I’ll get

and if I call obj with no parameters I will get a list of all my objects with the most recently modified objects first. This works with SQL 2005 and above.

We were changing our linked server and needed to test every page in our website that used that link server.  None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn call other stored procs that us it.    I need to find all of the pages that eventually would use it so that we could test them all.  The fact that it could be nested within stored proc calls made it a little more difficult.   Here is how I did it:

Step 1:  A simple stored proc used for searching for text within other stored procs. 

This work for SQL Server 2005

create proc dbo.Find(@search VARCHAR(100) = '') AS  
begin  
set nocount on  
  
SET @search = '%' + @search + '%'  
  
select object_name(o.object_id) Object, o.type
from sys.sql_modules m  
join sys.objects o on m.object_id = o.object_id  
where m.definition like @search  
order by 1  
  
end  

Step 2: Make a working table to store you work as you process the stored procs.

create table spDoc
(parentObj varchar(500),
obj varchar(500),
[type] varchar(3),
level int)

You will be storing the object name (stored proc, function, or trigger) in the column “obj”.  You will be storing the calling stored proc in the “parentObj” column. 

Step 3: Create a stored proc to process your search.

create proc doc(@obj varchar(256))
as
begin
set nocount on
declare @level int


if exists (select 1 from spDoc where parentObj = @obj)
    return  -- already done.

select @level = isnull(level,0)+1
from spDoc
where obj = @obj

if @level is null
    set @level = 1


--Find all of the objects that have the text of the current object.
insert into spDoc
(obj, [type])
exec Find @obj

-- The @obj is the parent of the rows just created.
update spDoc
set parentObj = @obj,
level = @level
where parentObj is null

--Delete where they are both the same.
delete from spDoc
where parentObj = @obj
and obj = @obj


--Loop thru each of the rows just created
--and recursively call this function.
DECLARE @currentObj varchar(256)

DECLARE @getObjs CURSOR

SET @getObjs = CURSOR FOR
select obj
from spDoc
where parentObj = @obj


OPEN @getObjs

FETCH NEXT
FROM @getObjs INTO @currentObj

WHILE @@FETCH_STATUS = 0
BEGIN
    exec doc @currentObj  -- recursive call

    FETCH NEXT
    FROM @getObjs INTO @currentObj
END

CLOSE @getObjs
DEALLOCATE @getObjs

end

If you were trying to document the object called “MyLink” you would call it like:

   doc MyLink

Step 4: Get your results.

Once the processing is done you only need a listing of the parent objects and this is the list that you would use to check your front end code.  

select distinct parentObj
from spDoc
order by 1

And that is all there is to it.

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data

 This second step is tedious and lends itself to automation.  So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc.  So the example:

createSPFromTable SourceTypes

create proc saveSourceTypes(@id int, @Name varchar(50), @DisplayOrder float, @Status char(1))

as

begin

set nocount on

if @id > 0

    update SourceTypes

    set Name = @Name,

    DisplayOrder = @DisplayOrder,

    Status = @Status

    where id = @id

else

begin

    insert into SourceTypes

    (Name, DisplayOrder, Status)

    values (@Name, @DisplayOrder, @Status)

 set @id = scope_identity()

end

select @id

return @id

end

Below is the stored proc that generates the basic save proc.   Not that you have to pass in the primary key column if it is not named "id".

create proc [dbo].[createSPFromTable](@TableName varchar(250), @idColumn varchar(25)=null)
as
set nocount on

declare @crlf char(2)
set @crlf = char(13)+char(10)

declare @parameterList varchar(8000)
set @parameterList='create proc save'+@TableName+'('

-- This converts sql type to c# type.
SELECT @parameterList=  @parameterList + case when lower(c.COLUMN_NAME) = isnull(@idColumn,'id') then '' else '@'+c.COLUMN_NAME+' ' + c.DATA_TYPE+
CASE c.DATA_TYPE
WHEN 'binary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'char' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'decimal' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'numeric' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nvarchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varbinary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
ELSE '' END+', ' end,
@idColumn = case when @idColumn is null and lower(c.COLUMN_NAME) = 'id' then c.COLUMN_NAME else @idColumn end
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+'as'+@crlf+'begin'+@crlf+'set nocount on'+@crlf+@crlf+@crlf

set @parameterList=@parameterList+'if @'+@idColumn +' > 0 ' + @crlf

set @parameterList=@parameterList+' update '+@TableName+ @crlf +' set'

SELECT @parameterList=@parameterList+' '+ c.COLUMN_NAME+' = @'+c.COLUMN_NAME+', '+ @crlf
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-4)+ @crlf+ ' where '+@idColumn +' = @'+@idColumn+ @crlf

set @parameterList=@parameterList+'else '+ @crlf +'begin'+@crlf+ ' insert into '+@TableName+@crlf +' ('

SELECT @parameterList=@parameterList+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf

set @parameterList=@parameterList+' values ('

SELECT @parameterList=@parameterList+'@'+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+@crlf

set @parameterList=@parameterList+' set @'+@idColumn+' = scope_identity()'+@crlf

set @parameterList=@parameterList+'end'+@crlf

set @parameterList=@parameterList+'select @'+@idColumn+@crlf

set @parameterList=@parameterList+'return @'+@idColumn+@crlf

set @parameterList=@parameterList+'end'+@crlf
print @parameterList

 

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data
    3. create the data access layer methods that call the stored procs.

This is a little tedious so I created SQL stored proc called createDALFromSP to generate a data access method from a stored proc.  

To create a DAL method for a stored proc called SaveRequest I would run this from SQL Analyzer:

      createDALFromSP 'SaveRequest'

An what it generates looks like the following:

public void SaveRequest(Int32 ID, String Name, Guid UserID, String Comments, Boolean IsSpecial, Double BuildOrder, String status, Boolean clear)
{
    SqlCommand cmd = GetNewCmd("dbo.SaveRequest");

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Name;
    cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
    cmd.Parameters.Add("@Comments", SqlDbType.VarChar, 8000).Value = Comments;
    cmd.Parameters.Add("@IsSpecial", SqlDbType.Bit).Value = IsSpecial;
    cmd.Parameters.Add("@BuildOrder", SqlDbType.Float).Value = BuildOrder;
    cmd.Parameters.Add("@status", SqlDbType.Char, 1).Value = status;
    cmd.Parameters.Add("@clear", SqlDbType.Bit).Value = clear;

    cmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
    ExecuteNonQuery(cmd);
}
 

Below is the stored proc createDALFromSP, it looks like a mess but it is basic stuff.  This works on SQL Server 2000 and 2005.

create  proc dbo.createDALFromSP(@procName varchar(250))
as
begin

declare @parameterList varchar(8000)
declare @isGet bit

select @isGet = case when lower(@procName) like 'get%' then 1 else 0 end

if @isGet = 1
    set @parameterList='public DataTable '+@procName+'('   
else
    set @parameterList='public void '+@procName+'('

-- get the signiture of the method itelf - so convert SQL Server data types to C# datatypes
SELECT @parameterList=@parameterList+(SELECT CASE DATA_TYPE
        WHEN 'bigint' THEN 'Int64'
        WHEN 'binary' THEN 'Binary'
        WHEN 'bit' THEN 'Boolean'
        WHEN 'char' THEN 'String'
        WHEN 'datetime' THEN 'DateTime'
        WHEN 'decimal' THEN 'Decimal'
        WHEN 'float' THEN 'Double'
        WHEN 'image' THEN 'Binary'
        WHEN 'int' THEN 'Int32'
        WHEN 'money' THEN 'Decimal'
        WHEN 'nchar' THEN 'String'
        WHEN 'ntext' THEN 'String'
        WHEN 'numeric' THEN 'Decimal'
        WHEN 'nvarchar' THEN 'String'
        WHEN 'real' THEN 'Single'
        WHEN 'smalldatetime' THEN 'DateTime'
        WHEN 'smallint' THEN 'Int16'
        WHEN 'smallmoney' THEN 'Currency'
        WHEN 'sql_variant' THEN 'String'
        WHEN 'sysname' THEN 'String'
        WHEN 'text' THEN 'String'
        WHEN 'timestamp' THEN 'Binary'
        WHEN 'tinyint' THEN 'Byte'
        WHEN 'uniqueidentifier' THEN 'Guid'
        WHEN 'varbinary' THEN 'Binary'
        WHEN 'varchar' THEN 'String'
                ELSE '!MISSING' END)+' '+substring(PARAMETER_NAME,2,300)+', ' 
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
order by ORDINAL_POSITION

if @@rowcount > 0
       set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'

-- This is the body of the method
-- Most of the work is to convert sql data types to SqlDbType while listing out the parameters.
select Line
from (
select -99 SortOrder, @parameterList Line
union all
select -98, '{'
union all
select -97, '    SqlCommand cmd = GetNewCmd("dbo.'+@procName+'");'
union all
select -96,''
union all
select ORDINAL_POSITION, '    cmd.Parameters.Add("'+PARAMETER_NAME+'", SqlDbType.'+
    (SELECT CASE DATA_TYPE
    WHEN 'bigint' THEN 'BigInt'
    WHEN 'binary' THEN 'Binary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'bit' THEN 'Bit'
    WHEN 'char' THEN 'Char, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'datetime' THEN 'DateTime'
    WHEN 'decimal' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'float' THEN 'Float'
    WHEN 'image' THEN 'Image'
    WHEN 'int' THEN 'Int'
    WHEN 'money' THEN 'Money'
    WHEN 'nchar' THEN 'NChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'ntext' THEN 'NText'
    WHEN 'numeric' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'nvarchar' THEN 'NVarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'real' THEN 'Real'
    WHEN 'smalldatetime' THEN 'SmallDateTime'
    WHEN 'smallint' THEN 'SmallInt'
    WHEN 'smallmoney' THEN 'SmallMoney'
    WHEN 'sql_variant' THEN 'Variant'
    WHEN 'text' THEN 'Text'
    WHEN 'timestamp' THEN 'Timestamp'
    WHEN 'tinyint' THEN 'TinyInt'
    WHEN 'uniqueidentifier' THEN 'UniqueIdentifier'
    WHEN 'varbinary' THEN 'VarBinary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'varchar' THEN 'VarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    ELSE '!MISSING'
                         END)+').Value = '+substring(PARAMETER_NAME,2,300)+';'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
union all
select 901,''
union all
SELECT 902,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.InputOutput;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'INOUT'
union all
SELECT 903,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.Output;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'OUT'
union all
select 904,case when @isGet = 1 then '    return GetTable(cmd);' else '    ExecuteNonQuery(cmd);' end
union all
select 905, '}'
 ) x
order by SortOrder


end
 

We found an issue with some of the older scripts having SET ANSI_NULLS OFF and this causes problems.  The ANSI_NULLS ON setting is a database option that determines how NULL comparisons are handled. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

So if I have:

        Select *
        from A
        inner join B
        on A.name = B.name

With ANSI_NULLS OFF every rows where NAME is null in A will match every row in B where NAME is null.   I can’t see when you would ever want this.  And SET ANSI_NULLS OFF in SQL 2008 and after will actually throw an error.

The second part of this problem is that for stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect.   

The third part of this problem is that when we combine our sql scripts into 1 big text file and if one of the scripts has SET ANSI_NULLS OFF then every subsequent stored proc and SQL statement is affected if it doesn’t define this setting itself.

So going forward I'm going to make sure my store proc scripts start with:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

This is generated for you automatically when you right click a stored proc and select “Modify” or “Script Stored Proc As…”

In one of our ASP.Net application we wanted to change where the AppSettings were stored from a config file to a database table.   Since this is a very large application (over 670 .cs files), the first requirement was that the majority of the changes needed to be done with a search and replace.   In the end the change looked like this:

    System.Configuration.ConfigurationSettings.AppSettings["AppVersion"]

Would be replaced by:

    cApp.AppSettings["AppVersion"];

BACKGROUND    
We had app settings in web.config and the environment specific settings were in an WebEnvironment.config file that was referenced by the web.config file.  So our appSettings section in our web.config looked like:
    <appSettings file="WebEnvironment.config">
        <add key="AppVersion" value="13.9"/>
…
    </appSettings>

The problem with this is that we have to do a release to change one of the settings.  Whenever we were changing a setting in the WebEnvironment.config  we had to give the deployment team a different file for each environment and have them rename the correct file depending on where they were deploying to.   So they had a WebEnvironment.configTEST, WebEnvironment.configQA, WebEnvironment.configPROD and WebEnvironment.configDR, this is a little awkward and prone to errors.

We also liked the fact that if the app settings lived in a table then we could give ourselves a maintenance page where we could change the values without doing a deployment.

TABLE
First I needed the table to hold the app setting.  This was simple enough:
    create table dbo.Config
    (Environment varchar(4),
    [Key] varchar(255),
    Value varchar(8000),
    Comment varchar(255),
    LastUpdated datetime default getdate())
    go

STORED PROC
Second a stored proc to retrieve the values depending on the environment. So this is:

    create proc dbo.GetConfig(@Environment varchar(4))
    as
    begin 

select [key], value
from AppConfig with(nolock)
where environment = @Environment
union all
select [key], value
from AppConfig with(nolock)
where environment = 'All'
and [key] not in
            (select env.[key]
            from AppConfig env with(nolock)
            where env.environment = @Environment
            )
order by [key]

    end
    go

SAMPLE DATA
Now insert some sample data:
insert into Config
(Environment, [Key], Value, Comment)
values ('Dev','AppVersion','13.9', 'Version of the application')

insert into Config
(Environment, [Key], Value, Comment)
values ('Dev','key1','value1', 'Test data')

insert into Config
(Environment, [Key], Value, Comment)
values ('Prod','key1','value1prod', 'Test data')

CONFIG CLASS
Now I need my new class that will hold the data, this will only load once.

using System.Data;
using System.Collections.Generic;

public class cConfig
{
    public Dictionary<string, string> AppSettings = new Dictionary<string,string>();

    public cConfig(string environment)
    {
        // the cApp.DAL is our data access layer and this just calls the stored proc and returns a table.
        foreach (DataRow dr in cApp.DAL.CommonData.GetConfig(environment).Rows)
        {
            AppSettings.Add(dr["Key"].ToString(), dr["Value"].ToString());
        }

    }

}

CLASS VARIABLE
Now we make the data available globally within the application and define it in such a way that it only insantiates the cConfig object once in a tread safe way.

using System;
using System.Collections.Generic;
using System.Configuration;

  public sealed class cApp
    {
        // This is the only time System.Configuration.ConfigurationManager.AppSettings is called.
        // The appSetting ApplicationEnv is in machine.config and will be one of the values “Dev”, “Test”, “QA”, “Prod” or “DR”
        static readonly cConfig _config = new cConfig(System.Configuration.ConfigurationManager.AppSettings["ApplicationEnv"]));

        public static Dictionary<string, string> AppSettings
        {
            get {
                return _config.AppSettings;
            }
        }
....


Now I can use this anywhere in my code by using the following:
        cApp.AppSettings["MySetting"];

We had a problem where our users would timeout for apparently no reason.  I monitored the SQL Server for a while and found that every once in a while there would be a lot of blocking going on.  So I need to find the cause of this and fix it.

If there was blocking going on, than there must have been exclusive locks somewhere in the chain of stored proc calls…. Right?

I walked thru the full list of stored procs that were called, and all of the subsequent stored procs, functions and views.  Sometimes this hierarchy was deep and even recursive.  

I was looking for any UPDATE or INSERT statements…. There weren’t any (except on temporary tables that only had the scope of the stored proc so they didn’t count.)

On further research I found the locking is caused by the following:

A.     If you use a SELECT INTO to create your temp table then SQL Sever places locks on system objects.  The following was in our getUserPrivileges proc:

                --get all permissions for the specified user
                select   permissionLocationId,
                    permissionId,
                    siteNodeHierarchyPermissionId,
                    contactDescr as contactName,
                    l.locationId, description, siteNodeId, roleId
                into #tmpPLoc
                from vw_PermissionLocationUsers vplu
                    inner join vw_ContactAllTypes vcat on vplu.contactId = vcat.contactId
                    inner join Location l on vplu.locationId = l.locationId
                where  isSelected = 1 and
                    contactStatusId = 1 and
                    vplu.contactId = @contactId

The getUserPrivileges proc is called with every page request (it is in the base pages.)   It was not cached like you might expect.   It doesn’t look like it, but the SQL above references 23 tables in the FROM or JOIN clauses.  None of these table have the “with(nolock)” hint on it so it is taking longer than it should.   If I remove the WHERE clause to get an idea of the number of rows involved it returns 159,710 rows and takes 3 to 5 seconds to run (after hours with no one else on the server.)

So if this stored proc can only be run one-at-a-time because of the lock, and it is being called once per page, and it holds the locks on the system tables for the duration of the select and temp table creation, you can see how it might be affecting the performance of the whole application.

  The fix for this would be:
1.    Use session level caching so this is only called once per session.
2.    Replace the SELECT INTO with code that creates the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.
3.    Put “with(nolock)”  on everything involved with this call.

The reason it gets recompiled is because the temp table gets created and then a lot of rows are deleted from it (if a @locationId or @permissionLocationId are passed in).    This will cause the stored proc to be recompiled on the SELECT that follows (yes, in the middle of running the stored proc.)     In other procs I’ve noticed a DECLARE CURSOR statement whose SELECT statement references a temporary table – this will force a recompile too.

For more info on recompilation see:
http://support.microsoft.com/kb/243586/en-us

The fix for this would be:
1.    Again, hit this stored proc far fewer times by using caching.
2.    Have the @locationId or @permissionLocationId filtering applied in the WHERE clause while the table is being created.
3.    Replace the temp tables with table variables – they result in fewer recompilations.

In one of the systems I work on we have the problem of needing to track the different timezones that our users are in.  In doing this we need to account for daylight savings time.
I see code like this used in many places:

select   ...
dbo.fn_convertFromUTC(@StartUTC, @timeZone) as startLocal,
dbo.fn_convertFromUTC(@EndUTC, @timeZone) as endLocal
from      ...

and thought this might have an impact on performance since the conversion function will fire twice for each row in the result set.  I came up with a function that takes 3 parameters: @start, @end (both in UTC time) and @timeZone and returns a table like the one below.  You would then joint to this table to do the conversion.  This will allow for the same functionality, but will only be fired once or twice per result set.


startTime          endTime   offset  isHr2
3/1/07 7:00     3/11/07 6:59    -5    0
3/11/07 7:00    11/4/07 6:59    -4    0
11/4/07 7:00    11/4/07 7:59    -5    1
11/4/07 8:00    11/5/07 9:00    -5    0

--Here is some test code that shows how this would be used:

declare @startViewUTC DateTime, @endViewUTC DateTime,  @timeZone char(4)
set @startViewUTC = '3/1/2007 7:00am'
set @endViewUTC = '11/5/2007 9:00am'
set @timeZone = 'EPT'  -- Eastern Prevailing Time


select ai.startTime,
ai.endTime,
dateadd(hh, tzStart.offset,ai.startTime) as startLocal,
dateadd(hh, tzEnd.offset,ai.endTime) as endLocal,
tzStart.isHr2, tzStart.offset, tzEnd.isHr2, tzEnd.offset
from      dbo.fn_getAllIntervals(@startViewUTC, @endViewUTC, 3) ai
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone)  tzStart
on ai.startTime between tzStart.startTime and tzStart.endTime
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone)  tzEnd
on ai.endTime between tzEnd.startTime and tzEnd.endTime


--And here is the functions that are used to return the time zone offset table:

-- First we need to have a function that returns the Daylight Saving Start DateTime for a given year.

CREATE ALTER  FUNCTION dbo.fn_getDSTStart (
    @year int
)
RETURNS datetime
AS
BEGIN
    DECLARE @dstStart datetime

    IF @year < 2007
        SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '0401')) % 7, Cast(@year AS varchar) + '0401'))
    ELSE
        SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '0308')) % 7,Cast(@year AS varchar) + '0308'))

    RETURN @dstStart
END

CREATE FUNCTION dbo.fn_getDSTEnd (
    @year int
)
RETURNS datetime
AS
BEGIN
    DECLARE @dstEnd datetime

    IF @year < 2007
        SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, 1 - DatePart(dw, Cast(@year AS varchar) + '1031'), Cast(@year AS varchar) + '1031'))
    ELSE
        SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '1101')) % 7,Cast(@year AS varchar) + '1101'))

    RETURN @dstEnd
END

CREATE       FUNCTION dbo.fn_getTimeZoneOffsets (
 @start datetime,
 @end datetime,
 @timeZone varchar(4)
)
RETURNS @timeZoneOffsets TABLE
 (startTime datetime,
  endTime datetime,
  offset int,
   isHr2 bit)
AS
BEGIN
declare @defaultOffset int

  --obtain the Standard Time, GMT Offset (this is not a complete list of timezones.)
  Select @defaultOffset =
        case @timeZone
      when 'GMT' then 0
      when 'AST' then -4
      when 'ADT' then -3
      when 'EST' then -5
      when 'EDT' then -4
      when 'CST' then -6
      when 'CDT' then -5
      when 'MST' then -7
      when 'MDT' then -6
      when 'PST' then -8
      when 'PDT' then -7
      when 'AKST' then -9
      when 'AKDT' then -8
      when 'EPT' then -5
      when 'CPT' then -6
      when 'MPT' then -7
      when 'PPT' then -8
        else 0
        end
 
 IF @timeZone IN ('CPT', 'EPT', 'MPT', 'PPT')
  BEGIN
  DECLARE @offsetTime int,
    @dstStart datetime,
    @dstEnd datetime,
    @yearStart datetime,
    @yearEnd datetime,
    @prevYearEnd datetime,
    @startYear int,
    @endYear int,
    @Year int
    
  set @startYear = year(@start)
  set @endYear = year(@end)
  set @Year = @startYear
  -- keep in mind these dates are all in GMT.
  --break the year into each part that has a different value
  while @Year <= @endYear
  begin
   set @dstStart = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTStart(@Year))
   set @dstEnd = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTEnd(@Year))
   set @yearStart = cast('01/01/'+cast(@Year as char(4)) as DateTime)

   set @yearEnd = cast('12/31/'+cast(@Year as char(4))+' 23:59:59.000' as DateTime)
   set @yearStart = case when @yearStart < @start then @start else @yearStart end
   set @yearEnd = case when @yearEnd < @end then @yearEnd else @end end


    if(@prevYearEnd is null)
    begin
       insert into @timeZoneOffsets
       Select @yearStart, dateadd(ss,-1,@dstStart), @defaultOffset, 0 isHr2
    end
    else
    begin
      update @timeZoneOffsets
       set endTime=dateadd(ss,-1,@dstStart)
       where endTime=@prevYearEnd
    end

   set @prevYearEnd = @yearEnd

   insert into @timeZoneOffsets
   Select @dstStart, dateadd(ss,-1,@dstEnd), @defaultOffset + 1, 0 isHr2
 
   insert into @timeZoneOffsets
   Select @dstEnd, dateadd(ss,-1,dateadd(hh,1,@dstEnd)), @defaultOffset, 1 isHr2
 
   insert into @timeZoneOffsets
   Select dateadd(hh,1,@dstEnd), @yearEnd, @defaultOffset, 0 isHr2
 
   set @Year = @Year + 1
 
  end

  delete from @timeZoneOffsets
  where endTime < @start
  or startTime > @end

    update @timeZoneOffsets
    set startTime = @start
    where startTime = (select min(startTime) from @timeZoneOffsets)

    update @timeZoneOffsets
    set endTime = @end
    where endTime = (select max(endTime) from @timeZoneOffsets)

 
   END
 ELSE
  insert into @timeZoneOffsets     
  Select @start, @end, @defaultOffset, 0 isHr2

RETURN
END

GO