Adam Howitt's Blog

WITH descendants(personid, parent_personid, generation) AS (
    /* Get the base case */
    SELECT personid, parent_personid, 0 as generation
    FROM family_members
    WHERE parent_personid IS NULL
    /* Union the recursive case */
    UNION ALL
    /* Find all family members (fm) who are descendants (d) of someone already in the descendants temp table */
    SELECT fm.personid, fm.parent_personid, generation+1
    FROM family_members fm
    INNER JOIN descendants d
    ON fm.parent_personid = d.personid
)
SELECT personid, parent_personid, generation
FROM descendants
/* Order the rows by generation ascending so that grandfather comes before father */
ORDER BY generation ASC

Person  Parent_Personid   Name
1       NULL              Grandfather
2       1                 Father
3       2                 Son

SELECT personid, parent_personid, 0 as generation
FROM family_members
WHERE personid = 1
UNION
SELECT personid, parent_personid, 0 as generation
FROM family_members
WHERE parent_personid = 1
UNION
SELECT personid, parent_personid, 0 as generation
FROM family_members
WHERE parent_personid IN (
    SELECT personid, parent_personid, 0 as generation
    FROM family_members
    WHERE parent_personid = 1
)

    SELECT *
    FROM any_table
    WHERE field_id = ISNULL(@field_id,field_id)

    SELECT *
    FROM any_table
    WHERE field_id = @field_id OR @field_id IS NULL

<h1>#skillObj.getTitle()#</h1>
<p>#skillObj.getBody()#</p>
<h2>Related Programmers</h2>
<ul>
<cfset stProgrammers=skillObj.getProgrammers()>
<cfloop collection="#stProgrammers#" item="programmer">
<cfset currentProgrammer=stProgrammers[programmer]>
<li>#currentProgrammer.getFirstName# #currentProgrammer.getLastName#<br/>
<p>#currentProgrammer.getShortBio()#</p></li>
</cfloop>
</ul>

INSERT INTO tb_content_history(history_id,etc..)
(SELECT NEWID(), etc.)

INSERT INTO tb_content_history(history_id,etc..)
(SELECT NEWID(), etc.)
SELECT @@identity as history_id

CREATE TRIGGER [dbo].[tr_history_id] ON [dbo].[tb_content_history]
FOR INSERT 
AS
SELECT history_id FROM INSERTED

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[usp_cms_select_paging_amh]
/*********************************************************************************
--** Stored Procedure Name: usp_cms_select_paging_amh
--********************************************************************************
--**
--** Description:
--**    Alternative to standard CMS paging
--** Revision History:
--**    AMH 06.01.05
--**    Updated to account for functional sort criteria e.g. CAST(a.attribute_desc as varchar(2000) DESC
--**    Extended notes:
    I've fixed the paging query again.  The issue is caused when you pass the sort order argument a
    function as one of the fields to sort by, for example:
    CAST(a.attribute_desc AS varchar(8000))',
    Which was getting incorrectly interpreted due to the extra spaces (the mask it originally used
    was "alias.column_name sort_direction".  Now it correctly acknowledges the sort order in this case.  
--** Example:
DECLARE
    @fieldNames varchar(1000),
    @tableName varchar(1000),
    @orderby varchar(100),
    @page int,
    @numRows int,
    @orderbyField varchar(100)
SET    @page = 2
SET     @numRows = 2
SET    @fieldNames = '*'
SET    @tableName = '(select top 3 * FROM vw_attorney_biography) ab'
SET     @orderby = 'ab.name DESC'
SET    @orderbyField = 'name'
EXEC usp_cms_select_paging_amh @fieldNames, '', @tableName, @page, @numRows, 1, '', @orderby, ''
*/
(
    @strFields VARCHAR(4000) ,
    @strPK VARCHAR(100),
    @strTables VARCHAR(4000),
    @intPageNo INT,
    @intPageSize INT,
    @blnGetRecordCount BIT,
    @strFilter VARCHAR(8000) ,
    @strSort VARCHAR(8000),
    @strGroup VARCHAR(8000)
)
AS
BEGIN
    DECLARE
        @topPage int,
        @theSql varchar(2000),
        @theCount varchar(2000),
        @blnBringAllRecords BIT,
        @strPageNo VARCHAR(50),
        @strPageSize VARCHAR(50),
        @strTopPage VARCHAR(50),
        @strFilterCriteria VARCHAR(8000),
        @strSimpleFilter VARCHAR(8000),
        @strSortCriteria VARCHAR(8000),
        @strGroupCriteria VARCHAR(8000),
        @oppositeSort1 VARCHAR(8000),
        @oppositeSort2 VARCHAR(8000),
        @intRecordcount INT,
        @intPagecount INT,
        @errMessage varchar(3000)    
    --vars for sort reversing
    DECLARE @this varchar(255),
        @rest varchar(8000),
        @pos int,
        @this_alias varchar(100),
        @this_column varchar(100),
        @this_direction varchar(100),
        @num_rows int
    --vars for recordcount
    DECLARE @totalRecordCount int,
        @ParmDefinition NVARCHAR(500),
        @SQLString NVARCHAR(4000)
    --********* CALCULATE THE LAST ROW TO SHOW ***************
    SET    @topPage = @intPageNo * @intPageSize
    --******** NORMALIZE THE FILTER AND SORTING CRITERIA
    --if they are empty, we will avoid filtering and sorting, respectively, executing more efficient queries
    IF @strFilter IS NOT NULL AND @strFilter != ''
        BEGIN
            SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
            SET @strSimpleFilter = ' AND ' + @strFilter + ' '
        END
    ELSE
        BEGIN
            SET @strSimpleFilter = ''
            SET @strFilterCriteria = ''
        END
    IF @strSort IS NOT NULL AND @strSort != ''
    BEGIN
        SET @oppositeSort1 = ''
        SET @oppositeSort2 = ''
        --CREATE A STRING WHICH REVERSES THE SORT ORDER
        SET @this = ' '
        SET @rest = @strSort
        SET @pos = PATINDEX('%,%', @rest)
        WHILE (@pos > 0)
        BEGIN
            set @this=ltrim(rtrim(substring(@rest,1,@pos-1)))
            set @rest=ltrim(rtrim(substring(@rest,@pos+1,len(@rest)-@pos)))
            -- function(alias.column text)
            IF PATINDEX('%(%.%',@this) > 0
            BEGIN
                IF PATINDEX('% asc%',lower(@this)) > 0
                BEGIN
                    SET @this_direction = 'asc'
                    SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% asc%',@this)-1)))
                END
                ELSE IF PATINDEX('% desc%',lower(@this)) > 0
                BEGIN
                    SET @this_direction = 'desc'
                    SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% desc%',@this)-1)))
                    PRINT 'here3'
                END
                ELSE
                    SET @this_direction = 'asc'
                IF PATINDEX('%.%',@this) = 0
                BEGIN
                    SET @this_alias = ''
                    SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
                END
                ELSE
                BEGIN
                    SET @this_alias = ''
                    SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
                END
            END
            -- alias.column dir         e.g. SELECT PATINDEX('%.% %','ab.hat  this')
            ELSE  IF PATINDEX('%.% %',@this) > 0
            BEGIN
                SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.% %',@this)-1)))
                SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
                SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
            END    
            -- alias.column         e.g. SELECT PATINDEX('%.%','ab.hat  this')
            ELSE IF PATINDEX('%.%',@this) > 0         
            BEGIN
                SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.%',@this)-1)))
                SET @this_column = ltrim(rtrim(right(@this,len(@this)-PATINDEX('%.%',@this))))
                SET @this_direction = 'ASC'
                
            END    
            -- column direction         e.g. SELECT PATINDEX('% %','ab  this')
            ELSE IF PATINDEX('% %',@this) > 0
            BEGIN
                SET @this_alias = ''
                SET @this_column = ltrim(rtrim(substring(@this,1,PATINDEX('% %',@this)-1)))
                SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
                
            END    
            -- column only         e.g. SELECT PATINDEX('%.% %','ab.hat')
            ELSE
            BEGIN
                SET @this_alias = ''
                SET @this_column = @this
                SET @this_direction = 'ASC'
                
            END    
            -- add this flipped column to the opposite sort list
            IF len(@oppositeSort1) != 0
            BEGIN
                SET @oppositeSort1 = @oppositeSort1 + ', '
                SET @oppositeSort2 = @oppositeSort2 + ', '
            END
            IF UPPER(@this_direction) = 'ASC'
            BEGIN
                SET @oppositeSort1 = @oppositeSort1 + @this_column + ' DESC'
                SET @oppositeSort2 = @oppositeSort2 + @this_column + ' ASC'
            END
            ELSE
            BEGIN
                SET @oppositeSort1 = @oppositeSort1 + @this_column + ' ASC'
                SET @oppositeSort2 = @oppositeSort2 + @this_column + ' DESC'
            END
            SET @pos= PATINDEX('%,%', @rest)
        END
        set @this=@rest
        -- NOTE: IDENTICAL CODE TO ABOVE
        -- function(alias.column text)
        IF PATINDEX('%(%',@this) > 0
        BEGIN
            IF PATINDEX('% asc%',lower(@this)) > 0
            BEGIN
                SET @this_direction = 'asc'
                SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% asc%',@this)-1)))
            END
            ELSE IF PATINDEX('% desc%',lower(@this)) > 0
            BEGIN
                SET @this_direction = 'desc'
                SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% desc%',@this)-1)))
                PRINT 'here3'
            END
            ELSE
                SET @this_direction = 'asc'
            IF PATINDEX('%.%',@this) = 0
            BEGIN
                SET @this_alias = ''
                SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
            END
            ELSE
            BEGIN
                SET @this_alias = ''
                SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
            END
        END
        -- alias.column dir         e.g. SELECT PATINDEX('%.% %','ab.hat  this')
        ELSE  IF PATINDEX('%.% %',@this) > 0
        BEGIN
            SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.% %',@this)-1)))
            SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
            SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
        END    
        -- alias.column         e.g. SELECT PATINDEX('%.%','ab.hat  this')
        ELSE IF PATINDEX('%.%',@this) > 0         
        BEGIN
            SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.%',@this)-1)))
            SET @this_column = ltrim(rtrim(right(@this,len(@this)-PATINDEX('%.%',@this))))
            SET @this_direction = 'ASC'
            
        END    
        -- column direction         e.g. SELECT PATINDEX('% %','ab  this')
        ELSE IF PATINDEX('% %',@this) > 0
        BEGIN
            SET @this_alias = ''
            SET @this_column = ltrim(rtrim(substring(@this,1,PATINDEX('% %',@this)-1)))
            SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
            
        END    
        -- column only         e.g. SELECT PATINDEX('%.% %','ab.hat')
        ELSE
        BEGIN
            SET @this_alias = ''
            SET @this_column = @this
            SET @this_direction = 'ASC'
            
        END    
        -- add this flipped column to the opposite sort list
        IF len(@oppositeSort1) != 0
        BEGIN
            SET @oppositeSort1 = @oppositeSort1 + ', '
            SET @oppositeSort2 = @oppositeSort2 + ', '
        END
        IF UPPER(@this_direction) = 'ASC'
        BEGIN
            SET @oppositeSort1 = @oppositeSort1 + @this_column + ' DESC'
            SET @oppositeSort2 = @oppositeSort2 + @this_column + ' ASC'
        END
        ELSE
        BEGIN
            SET @oppositeSort1 = @oppositeSort1 + @this_column + ' ASC'
            SET @oppositeSort2 = @oppositeSort2 + @this_column + ' DESC'
        END
        --SET @strOppositeSort = ' ORDER BY ' +
        SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
    END
    ELSE
        SET @strSortCriteria = ''
    IF @strGroup IS NOT NULL AND @strGroup != ''
        SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
    ELSE
        SET @strGroupCriteria = ''
    --****    GET THE RECORDCOUNT           *****
    IF @strGroupCriteria != ''
    BEGIN
        SET @ParmDefinition = N'@internalCount int OUTPUT'
        SET @SQLString = 'SELECT @internalCount = COUNT(*) FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
        
        exec sp_executesql @SQLString, @ParmDefinition, @internalCount = @totalRecordCount OUT
        IF (@@error!=0)
        BEGIN
            SET @errMessage = 'Error in dynamic sql clause - SELECT COUNT(*) AS SP_RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
            RAISERROR  20001 @errMessage
                RETURN(1)
        END
    END
    ELSE
    BEGIN
        SET @ParmDefinition = N'@internalCount int OUTPUT'
        SET @SQLString = 'SELECT @internalCount = COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria
        
        exec sp_executesql @SQLString, @ParmDefinition, @internalCount = @totalRecordCount OUT
        IF (@@error!=0)
        BEGIN
            SET @errMessage = 'Error in dynamic sql clause - SELECT COUNT(*) AS SP_RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria
                RAISERROR  20001 @errMessage
            RETURN(1)
        END
    END
    --******** NORMALIZE THE PAGING CRITERIA
    --if no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will set a flag that will help with that (blnBringAllRecords)
    IF @intPageNo < 1
        SET @intPageNo = 1
    SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
    
    IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO PAGING
        SET @blnBringAllRecords = 1
    ELSE
        BEGIN
            SET @blnBringAllRecords = 0
            SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
            SET @strPageNo =  CONVERT(VARCHAR(50), @intPageNo)
            SET @strTopPage = CAST(@topPage as varchar(5))
            IF @totalRecordCount - (@intPageSize * @intPageNo ) < 0
            BEGIN
                SET @strPageSize = cast(@totalRecordCount - ((@intPageNo - 1) * @intPageSize) as varchar(50))
                --SET @strPageSize = cast(1 as varchar(5))
            END
        END
    --************************** NOW START DOING THE REAL WORK
    IF @blnBringAllRecords = 1 --ignore paging and run a simple select
    BEGIN
    
        EXEC (
            'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
        )
    
    END-- WE HAD TO BRING ALL RECORDS
    ELSE        --BRING ONLY A PARTICULAR PAGE
    BEGIN
        IF @intPageNo = 1 --in this case we can execute a more efficient query, with no subqueries
        BEGIN
            EXEC (
                'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
            )
            IF (@@error!=0)
            BEGIN
                SET @errMessage = 'Error in dynamic sql clause - SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
                RAISERROR  20001 @errMessage
                    RETURN(1)
            END
        END
        ELSE --execute a structure of subqueries that brings the correct page
        BEGIN
            EXEC (
                'SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1  + ') page ORDER BY '+ @oppositeSort2
            )
            --SELECT @strTopPage
            --SELECT 'SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1  + ') page ORDER BY '+ @oppositeSort2
            IF (@@error!=0)
            BEGIN
                SET @errMessage = 'Error in dynamic sql clause - SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1  + ') page ORDER BY '+ @oppositeSort2
                RAISERROR  20001 @errMessage
                    RETURN(1)
            END
        END
            
    END --WE HAD TO BRING A PARTICULAR PAGE
    
    --IF WE NEED TO RETURN THE RECORDCOUNT
    IF @blnGetRecordCount = 1
        SELECT @totalRecordCount AS SP_RECORDCOUNT
    
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

row_number() OVER (ORDER BY mycol)

DECLARE @rowsPerPage int,
    @pageNum int,
 @startRow int,
 @endRow int

SET @rowsPerPage = 10
SET @pageNum = 3

SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
SET @endRow = @startRow + @rowsPerPage -1

SELECT * FROM (
 SELECT row_number() OVER (ORDER BY id) as resultNum, id
 FROM regTrackingHistory
) as numberResults
WHERE resultNum  BETWEEN @startRow AND @endRow

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.fn_getNumList (
/*
WRITTEN BY Adam Howitt adamhowitt@gmail.com
FUNCTION getDays
WRITTEN FOR SQL Server 2000
DATE 04.21.2006
Usage:
select currentNum
from fn_getNumList(0,60)
*/
@st_num int,
@num_to_add int )
RETURNS @numList TABLE ( currentNum int not null)
AS
BEGIN
    WHILE (@num_to_add > 0)
    BEGIN
        SET @st_num = 1+@st_num
        INSERT INTO @numList (currentNum)
        VALUES (@st_num)
        SET @num_to_add = @num_to_add - 1
    END
    RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

select t1.currentNum, ISNULL(t2.numOrders,0) as numberOfOrders
from fn_getNumList(0,60) t1
LEFT JOIN (SELECT datepart(month,create_dt) as dom, count(*) as numOrders
FROM order_tb
GROUP BY datepart(month,create_dt)) t2
ON t1.currentNum = t2.dom