DZone Snippets: sql-server code

Return current system date and time in Microsoft SQL Server

jeffreybarke (Jeffrey Barke) — Tue, 15 Jul 2008 05:28:42 GMT



SELECT GETDATE();

Search for specific text in all stored procedures

mrtrombone (Mark Easton) — Thu, 26 Apr 2007 03:08:05 GMT



declare @search varchar(50)

SET @search = 'searchterm'



SELECT    

     ROUTINE_NAME,

     ROUTINE_DEFINITION

FROM    

    INFORMATION_SCHEMA.ROUTINES

WHERE    

    ROUTINE_DEFINITION LIKE @search

ORDER BY

    ROUTINE_NAME

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.

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')

SqlServer table structure with Information_Schema

ms_michel (Michel) — Tue, 18 Oct 2005 17:45:28 GMT



SELECT COLUMN_NAME,

       DATA_TYPE,

       CHARACTER_MAXIMUM_LENGTH,

       NUMERIC_PRECISION,

       NUMERIC_SCALE,

       IS_NULLABLE,

       COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,

       COLUMN_DEFAULT

FROM   INFORMATION_SCHEMA.COLUMNS

WHERE  TABLE_NAME = 'xxxxxxxx'

ORDER BY ORDINAL_POSITION

Edit: The "system_function_schema.fn_datadictionary" snippet will create a system function in SQL Server that returns a data dictionary for any database on the server (http://www.bigbold.com/snippets/posts/show/1175).

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.

Date Literals in SQL

Will_Rickards (Will Rickards) — Wed, 21 Sep 2005 05:04:46 GMT

Due to international issues, there are many ways of representing a date in SQL code. I prefer to use odbc canonical.

dates



{d 'yyyy-mm-dd'}

{d '2001-12-31'}

timestamps



{ts 'yyyy-mm-dd hh:mm:ss'}

{ts '2001-12-31 00:00:00'}

times



{t 'hh:mm:ss'}

Don't let the ODBC in the name fool you, these work in Microsoft SQL Server, and through ODBC connections.
I've tested it in Query Analyzer, Stored Procedures, ADO code (both OLEDB and ODBC providers), ADO.Net code (both SQLClient and ODBC).

See Microsoft reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetdate_time_and_timestamp_literals.asp

It is a pity that there isn't some sort of SQL standard for date representation. Or maybe there is and I don't know it?

Define Identity value for SqlServer

ms_michel (Michel) — Wed, 21 Sep 2005 01:12:30 GMT



SET IDENTITY_INSERT MyTable ON;

INSERT INTO MyTable

    (MyIdentityField, MyFirstField, MySecondField)

VALUES

    (12345, 'ABCDE', 'etc...');

SET IDENTITY_INSERT MyTable OFF;

Literal DateTime for SqlServer

ms_michel (Michel) — Wed, 21 Sep 2005 00:42:19 GMT



CONVERT(DATETIME, 'yyyymmdd', 112)

112 -> ISO date
cf. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
----------
Edit: a better solution !



{d 'yyyy-mm-dd'}

Thanks to Wild Richard (http://www.bigbold.com/snippets/posts/show/756)

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]