This isn’t really big news, but I have had to look it up twice in the last quarter.  To find which stored procedures contain a specific text string, use:

SELECT Name, OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures sp
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Language%'

Example · SELECT · SQL Server · Stored Procedures · sys.procedures

I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database.  One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list.  Here is the SQL 2005/2008 code that I just created for use in a client environment:

CREATE FUNCTION [dbo].[fn_LinkedString]
(
	-- Add the parameters for the function here
	@ID varchar(10)
)
RETURNS nvarchar(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result nvarchar(1000)

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result= x.List
	FROM
		(
			SELECT
				L1.ID,
				List = SUBSTRING((SELECT (', '+ LinkedID)
										FROM Linked L2
										WHERE
											L1.ID = L2.ID
										ORDER BY
											ID,
											LinkedID
										FOR XML PATH('')
										), 3, 1000) FROM Linked L1
			GROUP BY ID
		) x
	WHERE
		x.ID = @ID
	-- Return the result of the function
	RETURN @Result

END

I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.

SQL Server · TSQL

I occasionally need to get some representative values from a table for testing and thought my solution might be something worth sharing.  I tend to think in nested selects and go back and clean up code into joins where appropriate.  Below you will see that I am using a group by and a min function to identify single instances of different types, and then pulling the required data in an outer select:

SELECT
	UserName
FROM
	Users
WHERE
	UserID in
	(
	SELECT
		MIN(UserID)
	FROM
		USERS
	WHERE
		Active = 1
	GROUP BY
		TypeCode
	)

Example · SQL Server · TSQL