No Split in SQL? Well, here's the method you need!

Splitting a string is an important work-around for not having array-type arguments to stored procedures and functions. Passing in a list of values for, say, a cross reference table value can save a lot of needless database calls by combining them in one and treating them as a single known quantity. Here's the code:

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_split]'))
DROP FUNCTION [dbo].[fn_split]
GO

CREATE  FUNCTION fn_Split(@text varchar(max), @delimiter char(1) = ',')
RETURNS @TABLE TABLE
(    
  [position] int IDENTITY PRIMARY KEY,
  [value] varchar(max)   
)
AS
BEGIN
 
DECLARE @index int 
SET @index = -1 

WHILE (LEN(@text) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @TABLE VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @TABLE VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END
GO

select 
     position
    ,convert(varchar(10), value) as value
from 
    dbo.fn_Split('dog,cat,pig,cow,rat', default)
order by 
    value desc

/*

output

position    value
----------- ----------
5           rat
3           pig
1           dog
4           cow
2           cat


*/

No DateSerial in SQL? Well, here's the function you need.

Yes, it is too bad there is no DateSerial in t-SQL. It's a nice function in Visual Basic. Three arguments (month, day, year) return as a combined date. Still, here's the function you need to have the equiv of DateSerial in SQL (I wrote this for SQL 2005, but the syntax should be the same for 2000).

DROP FUNCTION fn_DateSerial
GO

CREATE FUNCTION fn_DateSerial
(
    @Month int,
    @Day int,
    @Year int
)
RETURNS DateTime
AS
BEGIN
    return convert(datetime, 
        convert(varchar, @Month) + '/' + 
        convert(varchar, @Day) + '/' + 
        convert(varchar, @Year))
END
GO

set nocount on

select dbo.fn_DateSerial(1, 1, 2010) as Date
select dbo.fn_DateSerial(2, 10, 2020) as Date
select dbo.fn_DateSerial(3, 20, 2030) as Date
select dbo.fn_DateSerial(4, 30, 2040) as Date
select dbo.fn_DateSerial(null, 30, 2040) as Date
select dbo.fn_DateSerial(4, null, 2040) as Date
select dbo.fn_DateSerial(4, 30, null) as Date

/*

output

Date
-----------------------
2010-01-01 00:00:00.000

Date
-----------------------
2020-02-10 00:00:00.000

Date
-----------------------
2030-03-20 00:00:00.000

Date
-----------------------
2040-04-30 00:00:00.000

Date
-----------------------
NULL

Date
-----------------------
NULL

Date
-----------------------
NULL

*/

Current user in ASP.NET; it's NOT Environment.UserName

If you use Environment.UserName to get the current user in an ASP.Net application you will probably get the "Network Service" because that is the user running IIS (unless another user is running IIS).

If you want to get the current user, just do this:

public static string CurrentUserName
{
    get
    {
        System.Security.Principal.IPrincipal _User;
        _User = System.Web.HttpContext.Current.User;
        System.Security.Principal.IIdentity _Identity;
        _Identity = _User.Identity;
        string _Value;
        _Value = _Identity.Name.Substring(_Identity.Name.IndexOf(@"\")+1);
        return _Value;
    }
}
public static string CurrentDomain
{
    get
    {
        System.Security.Principal.IPrincipal _User;
        _User = System.Web.HttpContext.Current.User;
        System.Security.Principal.IIdentity _Identity;
        _Identity = _User.Identity;
        string _Value;
        _Value = _Identity.Name.Substring(0, _Identity.Name.IndexOf(@"\"));
        return _Value;
    }
}

ASP.NET Error Message

Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level.  This error can be caused by a virtual directory not being configured as an application in IIS.

If you get this error message it is PROBABLY because the virtual directory your web site is in has not been created as an Application. Open the properties of the IIS virtual directory and click the CREATE button; then try again.

The SQL JOIN types

Do you have trouble remembering the difference between an inner, outer and full join? You are not alone. Even I get them confused; so I put together this simple little list to remind me; maybe it will help you.

declare @x1 table(col1 int, col2 int)
insert into @x1
    select 11, 1 UNION ALL
    select 11, 2 UNION ALL
    select 11, 3 UNION ALL
    select 11, 4 UNION ALL
    select 11, 5 UNION ALL
    select 11, 6 

declare @x2 table(col2 int, col3 int)
insert into @x2
    select 3, 12 UNION ALL
    select 4, 12 UNION ALL
    select 5, 12 UNION ALL
    select 6, 12 UNION ALL
    select 7, 12 UNION ALL
    select 8, 12 

print 'data in x1'

select * from @x1

print 'data in x2'

select * from @x2

print 'inner join, only matching col2'

select 
    col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
    @x1 x1
    join @x2 x2 on x1.col2 = x2.col2

print 'left outer join, all x1 col2'

select 
    col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
    @x1 x1
    left outer join @x2 x2 on x1.col2 = x2.col2

print 'right outer join, all x2 col2'

select 
    col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
    @x1 x1
    right outer join @x2 x2 on x1.col2 = x2.col2

print 'full outer join, all x1/x2 col2'

select 
    col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
    @x1 x1
    full outer join @x2 x2 on x1.col2 = x2.col2

print 'cross join, all possible combinations'

select 
    col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
    @x1 x1
    cross join @x2 x2 

/*

data in x1
col1        col2
----------- -----------
11          1
11          2
11          3
11          4
11          5
11          6

(6 row(s) affected)

data in x2
col2        col3
----------- -----------
3           12
4           12
5           12
6           12
7           12
8           12

(6 row(s) affected)

inner join, only matching col2
col1        x1Xol2      x2Col2      col3
----------- ----------- ----------- -----------
11          3           3           12
11          4           4           12
11          5           5           12
11          6           6           12

(4 row(s) affected)

left outer join, all x1 col2
col1        x1Xol2      x2Col2      col3
----------- ----------- ----------- -----------
11          1           NULL        NULL
11          2           NULL        NULL
11          3           3           12
11          4           4           12
11          5           5           12
11          6           6           12

(6 row(s) affected)

right outer join, all x2 col2
col1        x1Xol2      x2Col2      col3
----------- ----------- ----------- -----------
11          3           3           12
11          4           4           12
11          5           5           12
11          6           6           12
NULL        NULL        7           12
NULL        NULL        8           12

(6 row(s) affected)

full outer join, all x1/x2 col2
col1        x1Xol2      x2Col2      col3
----------- ----------- ----------- -----------
11          1           NULL        NULL
11          2           NULL        NULL
11          3           3           12
11          4           4           12
11          5           5           12
11          6           6           12
NULL        NULL        7           12
NULL        NULL        8           12

(8 row(s) affected)

cross join, all possible combinations
col1        x1Xol2      x2Col2      col3
----------- ----------- ----------- -----------
11          1           3           12
11          2           3           12
11          3           3           12
11          4           3           12
11          5           3           12
11          6           3           12
11          1           4           12
11          2           4           12
11          3           4           12
11          4           4           12
11          5           4           12
11          6           4           12
11          1           5           12
11          2           5           12
11          3           5           12
11          4           5           12
11          5           5           12
11          6           5           12
11          1           6           12
11          2           6           12
11          3           6           12
11          4           6           12
11          5           6           12
11          6           6           12
11          1           7           12
11          2           7           12
11          3           7           12
11          4           7           12
11          5           7           12
11          6           7           12
11          1           8           12
11          2           8           12
11          3           8           12
11          4           8           12
11          5           8           12
11          6           8           12

(36 row(s) affected)

*/

SQL 2005 TSQL bug with NEWID() in DERIVED tables

I can't verify that this IS a bug, but I know what a bug is (I have created plenty in my career) and I think I can recognize one when I see it.

Explained

When you have a newid() in a derived table that joins to another table (or derived table, as my example), it appears that newid() executes again for each JOINED row even though it should execute once for each SOURCE row. Look at my example and see the pain:

create table x (col1 int, col2 int)
GO

insert into x select 1, 1
insert into x select 2, 1
insert into x select 3, 1
insert into x select 4, 1
insert into x select 5, 1
GO

select
     sub1.newid
    ,sub2.col1
    ,sub2.col2
from
    (select
         newid() as newid
        ,1 as jointothis) sub1
    join (select
         col1
        ,col2 from x) sub2
        on sub1.jointothis = sub2.col2
GO

drop table x

/*

expected output

newid                                col1        col2
------------------------------------ ----------- -----------
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 1           1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 2           1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 3           1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 4           1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 5           1

actual output

newid                                col1        col2
------------------------------------ ----------- -----------
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 1           1
A9D8E98B-8531-40F4-8D29-0BCEBBD066B0 2           1
88F36612-CED9-4847-B5B5-EFEBC6DF6EE8 3           1
15839554-E804-43D2-87D7-6A6C88D15A71 4           1
504E0E63-F7BC-4307-8131-0375F7149714 5           1

*/

Can you GROUP BY a CASE statement in SQL 2005?

Yes, you can:

create table x (col1 int, col2 int)
GO

insert into x select 1, 1
insert into x select 2, 1
insert into x select 3, 1
insert into x select 4, 1
insert into x select 5, 1
GO

select 
     count(*) as count
    ,(case 
        when col1 % 2 = 0 then 'Even' 
        else 'Odd' end) as what
from x
group by  (case 
        when col1 % 2 = 0 then 'Even' 
        else 'Odd' end)
GO

drop table x 

/*

output

count       what
----------- ----
2           Even
3           Odd

*/