DZone Snippets: sql_server code

Force recompile of Sql Server stored procs

MattScilipoti (Matt Scilipoti) — Thu, 01 Jun 2006 19:28:53 GMT

This force a recompile of all stored procs, views, tables, triggers, and functions in the current database.



CHECKPOINT 

GO



DBCC DROPCLEANBUFFERS

GO



DBCC FREEPROCCACHE

GO



DECLARE @intDBID INTEGER 

SET @intDBID = DB_ID()



DBCC FLUSHPROCINDB (@intDBID)

GO





declare @procNames Table (procName varchar(255))

insert into @procNames

select name from sysObjects where xtype in ('V','P','U','FN','TF','TR' ) and status > 0



--select * from @procNames



set nocount off

--3. Run each command

-- =============================================

-- Declare and using a READ_ONLY cursor

-- =============================================

DECLARE RecompilableItemsCursor CURSOR

READ_ONLY

FOR select procName from @procNames



DECLARE @RecompilableItem varchar(255)

OPEN RecompilableItemsCursor





FETCH NEXT FROM RecompilableItemsCursor INTO @RecompilableItem

WHILE (@@fetch_status <> -1)

BEGIN

	IF (@@fetch_status <> -2)

	BEGIN

		DECLARE @sql varchar(300)

		select @sql = 'Exec sp_recompile ' + @RecompilableItem

		print @sql

		exec (@sql)

	END

	FETCH NEXT FROM RecompilableItemsCursor INTO @RecompilableItem

END



CLOSE RecompilableItemsCursor

DEALLOCATE RecompilableItemsCursor





GO

A generic cross tab / pivot table query for Transact SQL (with sortable columns)

rgedwards (Rich Edwards (rich at semantise dot com)) — Tue, 09 May 2006 13:11:23 GMT

This procedure generates a 'cross-tab' (or 'pivot table') result set from (almost) any query, even a stored procedure and optionally allows you to specify a column sort order.

The procedure makes extensive use dynamic SQL and hence 'sp_executesql'. Because the scope of temporary tables does not extend outside of an sp_executesql transaction, I've had to use global temporary tables, which means that this procedure could become a bottleneck if used too frequently by multiple sessions.

This stored procedure is provided 'as is' and without any warranty - I've only tested it on a relatively small number of queries. For more information (and my contact details) see http://nwsoft.blogspot.com/2006/05/generic-cross-tab-pivot-table-query.html

This work is licensed under a Creative Commons Attribution 2.5 License.



CREATE PROCEDURE [dbo].[genericCrosstab]



 -- @tableSpec

 --   A comma delimited list of columns with datatypes (and null constraints, if required)

 --   e.g. 'col1 INT, col2 VARCHAR(255) NULL'

 --   N.B. You only need provide this list if the source of the data is a stored procedure

 @tableSpec    NVARCHAR(4000) = '',



 -- @sqlSelect

 --   The SELECT clause (or 'EXEC ' statement) for the input data

 --   e.g. 'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'

 --   e.g. 'EXEC dbo.myStoredProcedure , '

 @sqlSelect    NVARCHAR(512),



 -- @sqlFromWhere

 --   The FROM and WHERE clauses for the input data (if a SELECT statement)

 --   e.g. 'FROM authors a, titleauthor ta, titles t, stores s, sales WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title_id = sales.title_id AND s.stor_id = sales.stor_id'

 --   N.B. Leave this blank if the source of the data is a stored procedure

 @sqlFromWhere NVARCHAR(512)  = '',



 -- @pivotExpr

 --   The column name or expression to use as the pivot (specifies which values are the column headings for the crosstabbed data)

 --   e.g. 'store' or '''Store: '' + [store]'

 @pivotExpr    NVARCHAR(255),



 -- @valueExpr

 --   A column name or expression to use as the values in the crosstabbed data

 --   e.g. 'qty'

 @valueExpr    NVARCHAR(512),



 -- @function

 --   The aggregation function to use to combine values in the crosstabbed data

 --   e.g. 'MIN'

 --   N.B. If this parameter is left blank, the procedure will count up occurences and create additional column headings as necessary, e.g. '[X], [Y], [Y (2)], [Z]' if there are two occurences for label 'Y'

 @function     NVARCHAR(20)   = '',



 -- @groupBy

 --   The column list to group by (these columns will appear to the left of the crosstabbed data)

 --   e.g. 'au_id, au_fname, au_lname, phone'

 @groupBy      NVARCHAR(512),



 -- @sortLookup

 --   A table or view which can be queried to provide a sort order for the columns in the crosstabbed data

 --   e.g. 'days_of_the_week'

 --   N.B. The specified table or view must have 'label' and 'sort_order' columns

 --   N.B. If column order is irrelevant, leave this parameter blank

 --   N.B. Feature: if @sortLookup is given as '*', the columns are placed in ascending alphabetical order

 @sortLookup   NVARCHAR(40)   = '',



 -- @collation

 --   The collation sequence

 --   e.g. 'Latin1_general_CI_AS'

 @collation   NVARCHAR(40)   = 'Latin1_general_CI_AS',



 -- @verbose

 --   Set to 1 if you want debug messages

 @verbose     BIT            = 0



AS

BEGIN



 -- Check that the mandatory parameters are not empty strings

 IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0

 BEGIN

   RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)

   ROLLBACK TRANSACTION

 END



 IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0

 BEGIN

   RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)

   ROLLBACK TRANSACTION

 END



 IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0

 BEGIN

   RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)

   ROLLBACK TRANSACTION

 END



 IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0

 BEGIN

   RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)

   ROLLBACK TRANSACTION

 END



 -- Local variables

 DECLARE @i      INTEGER

 DECLARE @sql    NVARCHAR(4000)

 DECLARE @sqlX   NVARCHAR(4000)

 DECLARE @col    NVARCHAR(4000)

 DECLARE @pivot  NVARCHAR(4000)

 DECLARE @indx   NVARCHAR(10)

 DECLARE @cols   NVARCHAR(4000)

 DECLARE @where  NVARCHAR(4000)

 DECLARE @update NVARCHAR(4000)

 DECLARE @value  NVARCHAR(532)

 DECLARE @select NVARCHAR(4000)



 -- Drop the global temporary tables we will use (if they already exist)

 -- N.B. we have to use global temp tables, as local temp tables are dropped at the end of a batch (so are not accessible outside of the sp_executesql context)

 -- This syntax is a bit long winded because I couldn't make the substitution syntax for sp_executesql work (for this example only! - a bug in T-SQL?)

 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')

 EXEC sp_executesql @sqlX



 IF @verbose = 1 SELECT 'Global temporary tables dropped (if present).' AS [Message]



 -- * The real work starts here



 -- Store the input dataset

 IF LEN(RTRIM(@tableSpec)) = 0

 BEGIN

   -- Create and populate the temporary table in one step

   SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere

   IF @verbose = 1 SELECT @sql AS [Create and populate the temporary table in one step]

   EXEC sp_executesql @sql

 END

 ELSE

 BEGIN

   -- Create the temporary table first (the only method that can be used if the data is coming from a stored proc)

   SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'

   IF @verbose = 1 SELECT @sql AS [Create the temporary table]

   EXEC sp_executesql @sql

   -- Populate it

   SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere

   IF @verbose = 1 SELECT @sql AS [Populate the temporary table]

   EXEC sp_executesql @sql

 END

 IF @verbose = 1 SELECT * FROM ##input



 -- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!

 IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')

 BEGIN

   RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)

   ROLLBACK TRANSACTION

 END



 -- Add an identity column (we need a 'row ID')

 ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL



 -- Generate the column list

 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'

 IF @verbose = 1 SELECT @sql AS [Generate the column list]

 EXEC sp_executesql @sql

 IF @verbose = 1 SELECT * FROM ##colList



 -- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them

 IF RTRIM(ISNULL(@function, '')) = ''

 BEGIN

   SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation

 END

 ELSE

 BEGIN

   SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation

 END

 IF @verbose = 1 SELECT @sql AS [Create the ##grpCount table]

 EXEC sp_executesql @sql

 IF @verbose = 1 SELECT * FROM ##grpCount



 -- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)

 SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0



 -- Build the results table; one row per group

 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy

 IF @verbose = 1 SELECT @sql AS [Create the ##results table]

 EXEC sp_executesql @sql

 IF @verbose = 1 SELECT * FROM ##results



 -- Build the column list, taking into account duplicate occurences of pivotal values

 DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]

 OPEN xcursor



 FETCH NEXT FROM xcursor INTO @pivot, @indx



 WHILE @@FETCH_STATUS = 0

 BEGIN



  SELECT @i = 1



  -- Loop over indx

  WHILE @i <= @indx

  BEGIN



    -- Build the column list

    SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END

    INSERT INTO ##lookup VALUES (@pivot, @i, @col)

    SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'

    SELECT @cols =  ISNULL(@cols + ', ', '') + @col



    -- Add the column to the results table

    SELECT @sql = 'ALTER TABLE ##results ADD ' + @col

    IF @verbose = 1 SELECT @sql AS [Add column to the ##results table]

    EXEC sp_executesql @sql



    -- Continue

    SELECT @i = @i + 1



  END

  

  FETCH NEXT FROM xcursor INTO @pivot, @indx



 END



 CLOSE xcursor

 DEALLOCATE xcursor 



 IF @verbose = 1 SELECT * FROM ##lookup

 IF @verbose = 1 SELECT * FROM ##results



 -- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause

 DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]

 OPEN xcursor



 FETCH NEXT FROM xcursor INTO @indx, @col



 WHILE @@FETCH_STATUS = 0

 BEGIN

  

   -- Build the WHERE clause

   SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'

  

   FETCH NEXT FROM xcursor INTO @indx, @col



 END



 CLOSE xcursor

 DEALLOCATE xcursor 



 IF @verbose = 1 SELECT @where AS [WHERE clause]



 -- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards

 IF RTRIM(ISNULL(@sortLookup, '')) = ''

 BEGIN



   -- If no sorting table was specified, just select all columns

   SELECT @select = '*'



   -- Sort alphabetically

   SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'



 END

 ELSE

 BEGIN



   -- The 'group by' column(s) always come(s) first

   SELECT @select = @groupBy



   -- Sort in proscribed order

   SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'



 END



 IF @verbose = 1 SELECT @sql AS [Build the ##temp table]

 EXEC sp_executesql @sql 

 IF @verbose = 1 SELECT * FROM ##temp



 -- The value expression

 SELECT @value = @valueExpr



 -- If a function has been specified

 IF RTRIM(ISNULL(@function, '')) != ''

 BEGIN

   SELECT @value = @function + '(' + @value + ')'

 END



 DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp

 OPEN xcursor



 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot



 WHILE @@FETCH_STATUS = 0

 BEGIN



  -- Build the SELECT expression

  IF @select != '*' SELECT @select = @select + ', ' + @col



  -- Create the SET clause of the UPDATE sql

  IF RTRIM(ISNULL(@function, '')) = ''

  BEGIN

    -- No function specified

    SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'

  END

  ELSE

  BEGIN

    -- Function specified

    SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'

  END

  SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'

  IF @verbose = 1 SELECT @sql AS [Create the SET clause of the UPDATE sql]

  EXEC sp_executesql @sql

  

  FETCH NEXT FROM xcursor INTO @indx, @col, @pivot



 END



 CLOSE xcursor

 DEALLOCATE xcursor 



 -- Return the results

 SELECT @sql = 'SELECT ' + @select + ' FROM ##results'

 IF @verbose = 1 SELECT @sql AS [Create the SELECT statement that will return the results]

 EXEC sp_executesql @sql



 -- Tidy up: drop the global temporary tables

 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')

 EXEC sp_executesql @sqlX

 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')

 EXEC sp_executesql @sqlX



END

The following script creates two tables (one of test data, the other to specify a sort order) and runs the genericCrosstab procedure to generate a report.



IF EXISTS (SELECT *

             FROM dbo.sysobjects

            WHERE [id] = object_id(N'[dbo].[testTable]')

              AND OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

  DROP TABLE [dbo].[testTable]

END

GO



CREATE TABLE testTable

 ([who]  VARCHAR(10)  NOT NULL,

  [when] DATETIME     NOT NULL,

  [what] VARCHAR(255) NOT NULL,

  [qty]  INT          NOT NULL)

GO



DELETE FROM testTable

GO



INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  5)

INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Coding',  1)

INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Testing', 7)

INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  3)

INSERT INTO testTable VALUES ('Tim',  '2005-APR-01', 'Coding',  4)

INSERT INTO testTable VALUES ('Fred', '2005-APR-02', 'Coding',  8)

INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Coding',  2)

INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Testing', 6)

INSERT INTO testTable VALUES ('Tim',  '2005-APR-02', 'Coding',  4)

GO



SELECT * FROM testTable

GO



EXEC [dbo].[genericCrosstab]

 @sqlSelect    = 'SELECT *',

 @sqlFromWhere = 'FROM testTable',

 @pivotExpr    = 'what',

 @valueExpr    = 'qty',

 @function     = 'SUM',

 @groupBy      = 'who'

GO



-- Extending the example to proscribe column order...



IF EXISTS (SELECT *

             FROM dbo.sysobjects

            WHERE [id] = object_id(N'[dbo].[testSorter]')

              AND OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

  DROP TABLE [dbo].[testSorter]

END

GO



CREATE TABLE testSorter

 ([label]      VARCHAR(255) NOT NULL,

  [sort_order] INT          NOT NULL)

GO



DELETE FROM testSorter

GO



INSERT INTO testSorter VALUES ('Testing',  1)

INSERT INTO testSorter VALUES ('Coding',   2)

GO



SELECT * FROM testSorter

GO



EXEC [dbo].[genericCrosstab]

 @sqlSelect    = 'SELECT *',

 @sqlFromWhere = 'FROM testTable',

 @pivotExpr    = 'what',

 @valueExpr    = 'qty',

 @function     = 'SUM',

 @groupBy      = 'who',

 @sortLookup   = 'testSorter',

 @verbose      = 0

GO

You can change @verbose to 1 to see what is going on.

splitting a text list in sql

Will_Rickards (Will Rickards) — Fri, 24 Feb 2006 04:12:02 GMT

This goes along with my integer split procedure.
http://www.bigbold.com/snippets/posts/show/774

Often times I have a list of integers I need to pass to the database to get worked on. Such as checkboxes on a web page or some other list. I needed some TSQL that would take a text string and split it by a separator, in this case a comma. The following is the result of that need.
The way I normally use it is in a stored procedure like the one below with several text type arguments. This is a variation designed to split a list of strings separated by a special character sequence. Image two lists, one of the ids and one of the data. You parse the first list to get a table of the ids and you parse the second list to get the data and insert/update as appropriate.
http://www.bigbold.com/snippets/posts/show/774



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

   DROP PROCEDURE [dbo].[uspSplitTextList]

GO

                                      

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

-- uspSplitTextList

--

-- Description:

--		splits a separated list of text items and returns the text items

--

-- Arguments:

--		@list_text				- list of text items

--		@Delimiter				- delimiter

--

-- Notes:

-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks

--

-- History:

-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary

--

CREATE PROCEDURE uspSplitTextList

	@list_text				text,

   @Delimiter				varchar(3)

AS



SET NOCOUNT ON



DECLARE @InputLen			integer			-- input text length

DECLARE @TextPos			integer			-- current position within input text

DECLARE @Chunk				varchar(8000)	-- chunk within input text

DECLARE @ChunkPos			integer			-- current position within chunk

DECLARE @DelimPos			integer			-- position of delimiter

DECLARE @ChunkLen			integer			-- chunk length

DECLARE @DelimLen			integer			-- delimiter length

DECLARE @ItemBegPos		integer			-- item starting position in text

DECLARE @ItemOrder		integer			-- item order in list

DECLARE @DelimChar		varchar(1)		-- first character of delimiter (simple delimiter)



-- create table to hold list items

-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied

CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )



-- process list

IF @list_text IS NOT NULL

   BEGIN



	-- initialize

   SET @InputLen = DATALENGTH(@list_text)

   SET @TextPos = 1

	SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)

	SET @DelimLen = DATALENGTH(@Delimiter)

   SET @ItemBegPos = 1

   SET @ItemOrder = 1

   SET @ChunkLen = 1



   -- cycle through input processing chunks

   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0

      BEGIN



      -- get current chunk

      SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)



      -- setup initial variable values

      SET @ChunkPos = 1

      SET @ChunkLen = DATALENGTH(@Chunk)

      SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)



      -- loop over the chunk, until the last delimiter

      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0

         BEGIN



			-- see if this is a full delimiter

         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter

            BEGIN



				-- insert position

	         INSERT INTO #list_items (item_order, item_begpos, item_endpos)

	         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

	         

	         -- adjust positions

	         SET @ItemOrder = @ItemOrder + 1

	         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen

	         SET @ChunkPos = @DelimPos + @DelimLen



				END

         ELSE

            BEGIN



            -- adjust positions

            SET @ChunkPos = @DelimPos + 1



            END

      

         -- find next delimiter      

         SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)



         END



      -- adjust positions

      SET @TextPos = @TextPos + @ChunkLen



      END



	-- handle last item

   IF @ItemBegPos <= @InputLen

      BEGIN



      -- insert position

      INSERT INTO #list_items (item_order, item_begpos, item_endpos)

      VALUES (@ItemOrder, @ItemBegPos, @InputLen)



      END



	-- delete the bad items

   DELETE FROM #list_items

   WHERE item_endpos < item_begpos



   -- return list items

	SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos

   FROM #list_items

   ORDER BY item_order



   END



DROP TABLE #list_items



RETURN



/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

Convert a string representation of a UK date to a datetime

rgedwards (Rich Edwards (rich at semantise dot com)) — Mon, 14 Nov 2005 00:34:44 GMT

This function is not particularly smart - it expects a UK-style date string in 'dd/mm/yyyy' format, but can cope with 'dd/mm/yy' (and if mm > 12 it assumes the date is 'mm/dd/yyyy'). This work is licensed under a Creative Commons Attribution 2.5 License.



CREATE FUNCTION dbo.f_convert_str_to_date(@str VARCHAR(10))

  RETURNS DATETIME

AS

BEGIN



  -- Declare local variables

  DECLARE @boundary  TINYINT

  DECLARE @separator CHAR(1)

  DECLARE @return    VARCHAR(11)

  DECLARE @index1    INT

  DECLARE @index2    INT

  DECLARE @day       VARCHAR(2)

  DECLARE @month     VARCHAR(2)

  DECLARE @year      VARCHAR(4)

  DECLARE @iDay      TINYINT

  DECLARE @iMonth    TINYINT

  DECLARE @iYear     SMALLINT

  DECLARE @iSwap     TINYINT



  -- 'Constants'

  SELECT @boundary = 20

  SELECT @separator = '/'



  -- Indexes of forward slash separators (2 are expected)

  SELECT @index1 = CHARINDEX(@separator, @str)

  SELECT @index2 = CHARINDEX(@separator, @str, @index1 + 1)



  -- Get the day, month and year

  SELECT @day    = LTRIM(RTRIM(LEFT(@str, @index1 - 1)))

  SELECT @month  = LTRIM(RTRIM(SUBSTRING(@str, (@index1 + 1), (@index2 - @index1) - 1)))

  SELECT @year   = LTRIM(RTRIM(RIGHT(@str, (LEN(@str) - @index2))))



  -- Convert the values to integer representations (will throw an error if they can't be converted)

  SELECT @iDay   = CAST(@day AS TINYINT)

  SELECT @iMonth = CAST(@month AS TINYINT)

  SELECT @iYear  = CAST(@year AS SMALLINT)



  -- Swap the day and month if they're obviously in the wrong format

  IF @iMonth > 12

  BEGIN

    SELECT @iSwap  = @iMonth

    SELECT @iMonth = @iDay

    SELECT @iDay   = @iSwap

  END



  -- Convert back to string representations

  SELECT @day    = CAST(@iDay AS VARCHAR(2))

  SELECT @month  = CAST(@iMonth AS VARCHAR(2))

  SELECT @year   = CAST(@iYear AS VARCHAR(4))



  -- If the day and/or month and/or year are a single digit, prefix with a zero

  SELECT @day    = CASE WHEN LEN(@day)   = 1 THEN '0' + @day   ELSE @day   END

  SELECT @month  = CASE WHEN LEN(@month) = 1 THEN '0' + @month ELSE @month END

  SELECT @year   = CASE WHEN LEN(@year)  = 1 THEN '0' + @year  ELSE @year  END



  -- If the year is only 2 digits long, prefix with '19' or '20' (depending on the boundary)

  IF LEN(@year) = 2 SELECT @year = CASE WHEN @iYear < @boundary THEN '20' + @year ELSE '19' + @year END



  -- Build the cleaned up date string, with the month number converted to a string expression

  SELECT @return = @day + '-'

                 + CASE @month

                     WHEN '01' THEN 'JAN'

                     WHEN '02' THEN 'FEB'

                     WHEN '03' THEN 'MAR'

                     WHEN '04' THEN 'APR'

                     WHEN '05' THEN 'MAY'

                     WHEN '06' THEN 'JUN'

                     WHEN '07' THEN 'JUL'

                     WHEN '08' THEN 'AUG'

                     WHEN '09' THEN 'SEP'

                     WHEN '10' THEN 'OCT'

                     WHEN '11' THEN 'NOV'

                     WHEN '12' THEN 'DEC'

                   END

                 + '-' + @year



  -- Done

  RETURN CAST(@return AS DATETIME)



END

Example of usage:



SELECT dbo.f_convert_str_to_date('21/8/01')

Splitting an integer list in TSQL

Will_Rickards (Will Rickards) — Sat, 01 Oct 2005 03:35:03 GMT

Revised: 2006-02-23 - fixed various bugs by using new algorithm
Often times I have a list of integers I need to pass to the database to get worked on. Such as checkboxes on a web page or some other list. I needed some TSQL that would take a text string and split it by a separator, in this case a comma. The following is the result of that need.
The way I normally use it is in a stored procedure like the one below with several text type arguments. Sometimes I use a variation designed to split a list of strings separated by a special character sequence. Image two lists, one of the ids and one of the data. You parse the first list to get a table of the ids and you parse the second list to get the data and insert/update as appropriate.



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitIntegerList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

   DROP PROCEDURE [dbo].[uspSplitIntegerList]

GO

                                      

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

-- uspSplitIntegerList

--

-- Description:

--		splits a comma separated list of integers and returns the integer list

--

-- Arguments:

--		@list_integers					- list of integers

--

-- Notes:

-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks

--

-- History:

-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary

--

CREATE PROCEDURE uspSplitIntegerList

	@list_integers			text

AS



SET NOCOUNT ON



DECLARE @InputLen			integer			-- input text length

DECLARE @TextPos			integer			-- current position within input text

DECLARE @Chunk				varchar(8000)	-- chunk within input text

DECLARE @ChunkPos			integer			-- current position within chunk

DECLARE @DelimPos			integer			-- position of delimiter

DECLARE @ChunkLen			integer			-- chunk length

DECLARE @DelimLen			integer			-- delimiter length

DECLARE @Delimiter      varchar(3)		-- delimiter

DECLARE @ItemBegPos		integer			-- item starting position in text

DECLARE @ItemOrder		integer			-- item order in list



-- create table to hold list items

-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied

CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )



-- process list

IF @list_integers IS NOT NULL

   BEGIN



	-- initialize

   -- notice that this loop assumes a delimiter length of 1

   -- if the delimiter is longer we have to deal with stuff like delimiters straddling the chunk boundaries

   SET @InputLen = DATALENGTH(@list_integers)

   SET @TextPos = 1

	SET @Delimiter = ','

	SET @DelimLen = DATALENGTH(@Delimiter)

   SET @ItemBegPos = 1

   SET @ItemOrder = 1

   SET @ChunkLen = 1



   -- cycle through input processing chunks

   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0

      BEGIN



      -- get current chunk

      SET @Chunk = SUBSTRING(@list_integers, @TextPos, 8000)



      -- setup initial variable values

      SET @ChunkPos = 1

      SET @ChunkLen = DATALENGTH(@Chunk)

      SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)



      -- loop over the chunk, until the last delimiter

      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0

         BEGIN



			-- insert position

         INSERT INTO #list_items (item_order, item_begpos, item_endpos)

         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

         

         -- adjust positions

         SET @ItemOrder = @ItemOrder + 1

         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen

         SET @ChunkPos = @DelimPos + @DelimLen

      

         -- find next delimiter      

         SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)



         END



      -- adjust positions

      SET @TextPos = @TextPos + @ChunkLen



      END



	-- handle last item

   IF @ItemBegPos <= @InputLen

      BEGIN



      -- insert position

      INSERT INTO #list_items (item_order, item_begpos, item_endpos)

      VALUES (@ItemOrder, @ItemBegPos, @InputLen)



      END



	-- delete the bad items

   DELETE FROM #list_items

   WHERE item_endpos < item_begpos



   -- return list items

	SELECT CAST(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1)) AS integer) AS item_integer, item_order, item_begpos, item_endpos

   FROM #list_items

   WHERE ISNUMERIC(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1))) = 1

   ORDER BY item_order



   END



DROP TABLE #list_items



RETURN



/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

Replace multiple spaces with single spaces

rgedwards (Rich Edwards (rich at semantise dot com)) — Thu, 29 Sep 2005 23:22:32 GMT

This work is licensed under a Creative Commons Attribution 2.5 License.



WHILE CHARINDEX('  ', @string) > 0 SELECT @string = REPLACE(@string, '  ', ' ')

where @string is the parameter or variable.

Is a specified year a leap year?

rgedwards (Rich Edwards (rich at semantise dot com)) — Wed, 14 Sep 2005 22:56:27 GMT

This function returns a 1 if the supplied year is a leap year, otherwise 0



CREATE FUNCTION dbo.f_is_leap_year(@year INTEGER)

RETURNS BIT AS 

BEGIN



  IF @year % 400 = 0

     -- Years divisible by 400 (e.g. 1600, 2000) are always leap years

     RETURN 1 

  ELSE

  BEGIN

    IF @year % 100 = 0

       -- Years not divisible by 400 but divisible by 100 (e.g. 1900) are never leap years

       RETURN 0

    ELSE

    BEGIN

      IF @year % 4 = 0

         -- Years not divisible by 400 or 100 but divisible by 4 (e.g. 1976) are always leap years

         RETURN 1

      ELSE

         RETURN 0

    END

  END



  -- The following statement should never be reached (but the SQL syntax parser requires it)

  RETURN 0 



END

GO



SELECT dbo.f_is_leap_year(2003) AS [2003], dbo.f_is_leap_year(2004) AS [2004], dbo.f_is_leap_year(2005) AS [2005]

Convert a sentence to title case (i.e. each word has a capitalised initial letter)

rgedwards (Rich Edwards (rich at semantise dot com)) — Wed, 14 Sep 2005 22:47:34 GMT



CREATE FUNCTION dbo.f_convert_to_title_case

  (@string VARCHAR(255))

RETURNS VARCHAR(255) AS 

BEGIN 



  -- Declarations

  DECLARE @return VARCHAR(255)

  DECLARE @len INT

  DECLARE @finished BIT

  DECLARE @pos INT

  DECLARE @thischar CHAR(1)

  DECLARE @thisasc INT

  DECLARE @capitalise BIT



  -- Initialise

  SELECT @finished = 0

  SELECT @string = LOWER(@string)

  SELECT @return = @string

  SELECT @len = DATALENGTH(@return)



  -- Failsafe

  IF @len = 0

     SELECT @finished = 1



  -- Loop over all characters: capitalise first character and those after spaces, replace underscores with spaces

  SELECT @pos = 0

  SELECT @capitalise = 1

  WHILE @finished = 0

  BEGIN



    -- Next position in string

    SELECT @pos = @pos + 1



    -- Done?

    IF @pos > @len

       SELECT @finished = 1

    ELSE

    BEGIN

       -- Read this character

       SELECT @thischar = SUBSTRING(@return, @pos, 1)

       SELECT @thisasc  = ASCII(@thischar)

       IF @thischar IN ('_', ' ')

       BEGIN

          SELECT @return = LEFT(@return, @pos - 1) + ' ' + RIGHT(@return, @len - @pos)

          SELECT @capitalise = 1

       END

       ELSE

       BEGIN

          IF @capitalise = 1 AND (@thisasc BETWEEN 97 AND 122)

          BEGIN

             SELECT @thisasc = @thisasc - 32

             SELECT @return = LEFT(@return, @pos - 1) + CHAR(@thisasc) + RIGHT(@return, @len - @pos)

             SELECT @capitalise = 0

          END

       END

    END

  END



  -- Done

  RETURN @return



END

GO

Example of usage:



SELECT dbo.f_convert_to_title_case('mY ExAmPlE strinG') AS [result]

Convert a number from any Base (between 2 and 36) to Base 10

rgedwards (Rich Edwards (rich at semantise dot com)) — Wed, 14 Sep 2005 22:42:23 GMT

@base can be anything from 2 to 36. This work is licensed under a Creative Commons Attribution 2.5 License.



CREATE FUNCTION dbo.f_convert_to_base10

  (@string VARCHAR(255), @base TINYINT)

RETURNS INT AS 

BEGIN



  -- Declarations

  DECLARE @return INT

  DECLARE @len INT

  DECLARE @finished BIT

  DECLARE @pos INT

  DECLARE @thischar CHAR(1)

  DECLARE @thisasc INT

  DECLARE @val INT



  -- Initialise

  SELECT @base     = CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END

  SELECT @return   = 0

  SELECT @finished = 0

  SELECT @string   = UPPER(@string)

  SELECT @len      = DATALENGTH(@string)



  -- Failsafe

  IF @len = 0

     SELECT @finished = 1



  -- Loop over all characters: capitalise first character and those after spaces, replace underscores with spaces

  SELECT @pos = 0



  WHILE @finished = 0

  BEGIN



    SELECT @pos = @pos + 1



    IF @pos > @len



       -- If we've run out of characters, we're done

       SELECT @finished = 1



    ELSE

    BEGIN



       -- Get the character (from right to left)

       SELECT @thischar = SUBSTRING(@string, (@len - (@pos - 1)), 1)



       -- Get the character's ASCII value

       SELECT @thisasc  = ASCII(@thischar)



       -- Convert to a numerical value

       SELECT @val = CASE

                       WHEN @thisasc BETWEEN 48 AND 57 -- '0' AND '9'

                         THEN @thisasc - 48

                       WHEN @thisasc BETWEEN 65 AND 90 -- 'A' (= decimal 10) AND 'Z'

                         THEN @thisasc - 65 + 10

                       ELSE 0 END



       -- Add this portion on

       SELECT @return = @return + (POWER(@base, (@pos - 1)) * @val)



    END



  END



  -- Done

  RETURN @return



END

GO

Example of usage:



SELECT 'FFFF' AS [hex], dbo.f_convert_to_base10('FFFF', 16) AS [decimal]

Convert an integer from Base 10 to another Base (e.g. Hex or Binary)

rgedwards (Rich Edwards (rich at semantise dot com)) — Wed, 14 Sep 2005 22:37:47 GMT

@base can be anything from 2 to 36 (because then we can stick to the alphanumerics 0 to 9 and A to Z).
This work is licensed under a Creative Commons Attribution 2.5 License.



CREATE FUNCTION dbo.f_convert_from_base10

  (@num INT, @base TINYINT)

RETURNS VARCHAR(255) AS 

BEGIN 



  -- Declarations

  DECLARE @string VARCHAR(255)

  DECLARE @return VARCHAR(255)

  DECLARE @finished BIT

  DECLARE @div INT

  DECLARE @rem INT

  DECLARE @char CHAR(1)



  -- Initialise

  SELECT @string   = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

  SELECT @return   = CASE WHEN @num <= 0 THEN '0' ELSE '' END

  SELECT @finished = CASE WHEN @num <= 0 THEN 1 ELSE 0 END

  SELECT @base     = CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END



  -- Loop

  WHILE @finished = 0

  BEGIN



    -- Do the maths

    SELECT @div = @num / @base

    SELECT @rem = @num - (@div * @base)

    SELECT @char = SUBSTRING(@string, @rem + 1, 1)

    SELECT @return = @char + @return

    SELECT @num = @div



    -- Nothing left?

    IF @num = 0 SELECT @finished = 1



  END



  -- Done

  RETURN @return



END

GO

Example of usage:



SELECT 255 AS [decimal], dbo.f_convert_from_base10(255, 16) AS [hex], dbo.f_convert_from_base10(255, 2) AS [binary], dbo.f_convert_from_base10(255, 36) AS [base 36]