John Mendez

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

Microsoft SQL

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

Microsoft SQL

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

Microsoft SQL