SQL – How to get a row of results into a comma delimited list using FOR XML PATH and STUFF
Okay had this problem come up at work recently and it’s fantastic solution. It’s a bit hard to explain when you would need it, I guess it works well for when you’re doing table joins and need quick reference data out within a query.
Anyway here’s how you do it.
Firstly I’ve got a table ‘Test’ that contains two columns ID & Name. I’ve added some data in… Continue reading
How to do REPLACE string UPDATES in MySQL
So recently I updated some sql code for my blog, anyway I thought I’d share so here it is.
1
Basically go and look for every blog post where it had the old url in the content and replace it with the new url. Best to try it out on one link before you go and… Continue reading
How to check for active transactions
Debugging an issue today at work, and needed to find if there was any uncommitted transactions or active transactions.
There’s a couple of ways to do this:
1
Thanks Pinal Dave – more from his blog post here.
Also you can use dbcc opentran Or SELECT @@TRANSCOUNT
So, if you’re like me and what to see this in action… Continue reading
How to find the name of a sql server
Fast way to get a sql server name, great if you’re dealing with multiple sql servers.
1
This will return the Windows computer name on which the sql server instance is currently running on.
It will return NULL if there is an error.
1
This will return the instance & computer name
[code… Continue reading
how to select columns from a stored proc
Okay so recently I’ve had to select only certain columns from a large stored proc that may return like 4 columns. How to do this?
DECLARE @tableVar table (Surname varchar (100), FirstName varchar(255), Email varchar(100), Age int)
INSERT INTO @tableVar EXEC dbo.usp_getSomeData
SELECT Surname, Email FROM @tableVar… Continue reading
Passing a nullable/blank field to a WHERE statement
I’ve used this piece of code in many places lately to check if a field is nullable or blank – used mainly as a default value.
AND
(
@ResultType = ” OR
@ResultType ” AND ResultType IN(@ResultType)
)… Continue reading
grant execute for a stored proc to application role
simple script incase you forget:
grant exec on to… Continue reading
Using DELETE FROM with INNER JOIN
Ever needed to delete a table? Oh wait it’s joined to another. Well here’s some how you do it… the right way!
For MSSQL
DELETE Users
FROM Users u
INNER JOIN
Profiles p on u.UserID = p.UserID
WHERE u.UserIsArchived = 1
For MySQL
DELETE u, p
FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID
WHERE u.UserIsArchived = 1… Continue reading
Example script for create table in h2
Just figured I’d add one of these scripts incase I forget! Haha… but just if you’re curious, it not different to MySQL.
DROP TABLE IF EXISTS Players;
CREATE TABLE Players
(
PlayerID int identity,
FirstName varchar(255),
LastName varchar(255)
);
INSERT INTO Players
(FirstName, LastName)
VALUES
(‘Joey’, ‘Daly’);
Fusegrid SDK
Been using Fusegrid SDK, it’s fantastic for people who just want to start coding Coldfusion with zero time wasted on setup.
Normally you’ll need to setup a web server, then download ColdFusion and then work out what database you want… painful!
Fusegrid comes bundled with Railo + H2 Database, and CFWheels 
Get it here: http://www.fusegrid.com/sdk/… Continue reading
Email Subscription
Latest Tweets
@JoeyDaly there that cares for you no matter what and you just have to find her trust me 
- posted on 21/04/2013 16:14:08
Recent Comments
- Joey on CodeIgniter – Bonfire
- Bob on CodeIgniter – Bonfire
- rellintoppext on Myer biggest boxing day sales EVER
- bali ratih on XAMPP running very slow on localhost…
- joey on Google Maps, now indoors
Categories
- C# (30)
- CF (30)
- mach ii (1)
- CSS (6)
- Databases (13)
- TSQL (1)
- HTML5 (3)
- iPhone (17)
- Java (3)
- PHP (6)
- Stuff (104)
- Tennis (2)
Tags
Advertising
Archives
- January 2013
- December 2012
- November 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- September 2009
- July 2009
- May 2009