I always take for granite the usuability of the CASE statement in T-SQL. It is so useful and I should be using it more often.

For example I was working on a query the other day and of course I ended up using a CASE statement. The CASE statement is so flexible.

The example below is showing a nested CASE Statement in T-SQL:

CASE 
   WHEN @YourValue = CAST(ColumnName1 AS INT) 
	THEN CASE ColumnName1_1
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
		ELSE ''
	     END
   WHEN @YourValue = CAST(ColumnName2 AS INT) 
	THEN CASE ColumnName2_1
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
		ELSE ''
             END
   ELSE ''
END AS 'YourColumnName'

This proved to be very useful because I was taking the value of @YourValue and needed to see what value it matched against 2 seperate columns in the database.

If there was a match, I then wanted to do a nested CASE and see what value of another column was. In this example, you can see I just needed to output a Yes, No or blank.

Anyway, I just wanted to post this real quick to remind myself, and others to stick to simple concepts :)

I was unaware that you could accomplish something like this so I thought I would add to the articles out there just in case this will help others for a situation similiar to mine.

I wrote a stored procedure that would or would not return a record result based off conditions.

I then needed to integrate that with another stored procedure to use the results from it.

Now, I could have probably just wrote a Table Value Function in SQL, but I am also using this stored procedure for other parts of the applications through out the system.  Anyway, this is how you can insert the values from a stored procedure into a table.

DECLARE @tmpTable TABLE 
(
   ID INT
    ,FirstName VARCHAR(100)
    ,LastName VARCHAR(100)
)

INSERT INTO @tmpTable 
EXEC [dbo].[GetSomeData]

SELECT * FROM @tmpTable

The thing I found is that the column count has to match for the temp table and the columns being returned from the stored procedure


Note: My environment for this was SQL 2005

Ok this will be a short post, but I am posting this because it is something I overlooked when doing a clean install of Windows 7 on my desktop computer. Besides being a basic desktop computer, this desktop runs my SQL Server. Well since I put Windows 7 on, I figured I would do an install of SQL Server 2008 and get rid of SQL Server 2005.  Anyway, after putting all my databases back I was having problems connecting to it via my laptop...which is also running Windows 7 with an instance of SQL 2008. Now to the problem.  I could not connect to the SQL 2008 database on my desktop remotely via SQL Server Management Studio from my laptop.  I think to myself...no worries I'll go enable it.

I did the following on my desktop. 

• Opened SQL Server Management Studio
• Right Clicked the Server and went to properties
• Selected Connections on the left hand side
• Enabled "Allow remote connections to this server"

Simple enough right?

Not so much.  I forgot about Windows 7 firewall being enabled!!!  My choice was to completely disable it (which wouldn't be a horrible thing since I'm behind a router/firewall), however I wanted to keep it enabled.

Here is what to do.  Open up TCP Port 1433 on your Windows firewall and all is well.

• Go to the Advanced Settings of your Windows Firewall
• Click on Inbound Rules
• Click New Rule
• Rule Type is Port
• Apply to TCP only
• enter 1433 for the Specific Local Ports
• Finish the rest of the wizard and you should be good to go!

Here is a good resource for SQL Server and Firewall

http://msdn.microsoft.com/en-us/library/cc646023%28v=SQL.100%29.aspx

This simple user function will return the beginning of the day given a datetime in SQL. 

DECLARE @finalDate DATETIME

SET @finalDate = (SELECT CAST(DATEPART(mm,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(dd,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(yyyy,@date) AS VARCHAR(10)))

RETURN @finalDate

You can test it with the following:

Here is the second post that has to do with converting a julian date to a datetime  This only works for dates greater than October 15th 1582 at 12:00:00.

ALTER FUNCTION [dbo].[fnConvertJulianToDate]
(
 @JD DECIMAL(18,5)
)
RETURNS DATETIME
AS
BEGIN

 DECLARE @j INT
 DECLARE @g INT
 DECLARE @dg INT
 DECLARE @c INT
 DECLARE @dc INT
 DECLARE @b INT
 DECLARE @db INT
 DECLARE @a2 INT
 DECLARE @da INT
 DECLARE @y2 INT
 DECLARE @m2 INT
 DECLARE @d INT
 DECLARE @year INT
 DECLARE @month INT
 DECLARE @day INT
 DECLARE @hr INT
 DECLARE @min INT
 DECLARE @sec INT
 DECLARE @frac DECIMAL(18,5)
 DECLARE @f DECIMAL(18,2)
 DECLARE @finalDateTime DATETIME


 SET @frac =  @JD - FLOOR(@JD) + 0.5

 IF(@frac >= 1.0)
  BEGIN
   SET @frac = @frac - 1.0
   SET @JD = @JD + 1.0
  END

 SET @j = FLOOR(@JD) + 32044
 SET @g = @j / 146097
 SET @dg = @j % 146097
 SET @c = (@dg / 36524 + 1) * 3 / 4
 SET @dc = @dg - @c * 36524
 SET @b = @dc / 1461
 SET @db = @dc % 1461
 SET @a2 = (@db / 365 + 1) * 3 / 4
 SET @da = @db - @a2 * 365
 SET @y2 = @g * 400 + @c * 100 + @b * 4 + @a2
 SET @m2 = (@da * 5 + 308) / 153 - 2
 SET @d = @da - (@m2 + 4) * 153 / 5 + 122
 SET @year = @y2 - 4800 + (@m2 + 2) / 12
 SET @month = (@m2 + 2) % 12 + 1
 SET @day = @d + 1.5


 SET @hr  = FLOOR(@frac * 24.00)
 SET @min  = FLOOR((@frac*24.00 - @hr)*60.00)
 SET @f  = ((@frac*24.00 - @hr)*60.00 - @min)*60.00
 SET @sec = FLOOR(@f)
 SET @f = @f - @sec

 IF( @f > 0.5 )
  BEGIN
   SET @sec = @sec + 1
  END

 SET @finalDateTime = CONVERT(VARCHAR(20), CAST(@month AS VARCHAR(2))
    + '/'
    + CAST(@day AS VARCHAR(2))
    + '/'
    + CAST(@year AS VARCHAR(4))
    + ' '
    + CAST(@hr AS VARCHAR(2))
    + ':'
    + CAST(@min AS VARCHAR(2))
    + ':'
    + CAST(@sec AS VARCHAR(2)))

 RETURN @finalDateTime

END

Again here are the reference for the math and testing

http://aa.usno.navy.mil/data/docs/JulianDate.php
http://www.onlineconversion.com/julian_date.htm
http://en.wikipedia.org/wiki/Julian_day

Also to test it on your SQL Server just run the following queries once the functions are made

SELECT dbo.fnConvertDateToJulian(getDate())

SELECT dbo.fnConvertJulianToDate(dbo.fnConvertDateToJulian(getDate()))