Add DB Role IF NOT EXISTS

Check if the Database Role exists before creating it:

DECLARE @Role varchar(20) = 'UserRole'
IF NOT EXISTS (SELECT name FROM sysusers
WHERE issqlrole=1 AND name = @Role)
EXEC sp_addrole @Role; --EXEC sp_addrole @Role, 'dbo'

(TSQL) Escape Underscore in Like

SELECT * FROM Table WHERE Column LIKE '_a_'

The above code will generate results that has any one character before and after 'a' such as:
mac
cat
sad

SELECT * FROM Table WHERE Column LIKE '%[_]a[_]%'

The above code will generate results such as:
in_a_house
she_is_a_mother
make_a_living

T-SQL SELECT Random Rows in UNION

To Select random rows from a Table, use the ORDER BY NEWID()

SELECT * FROM Table ORDER BY NEWID();

SELECT TOP 10 PERCENT * FROM Table ORDER BY NEWID();

SELECT TOP 10 * FROM Table ORDER BY NEWID();

To Select random rows from a Table UNION Table, then do:

SELECT * FROM (SELECT TOP 10 Column1, Column2, Column 3 FROM Table1 ORDER BY NEWID()) TableA
UNION
SELECT * FROM (SELECT TOP 10 Column1, Column2, Column3 FROM Table2 ORDER BY NEWID()) TableB;

Dynamic MIME Type

Storing and retrieving images from the database is now made pretty easy.

But what about displaying images in Reports? We are required to provide an image type if we are to choose Database as our Image source.

This can be easily done if you an additional column of ImageName in your database alongside your Picture column. The ImageName should contain the proper extension of the image that was loaded into the database. A proper ImageName input, for example, is "megan-fox-is-a-dude.jpg".

On your Dataset query statement, include the following code:

SELECT Table.Column1, Table.Column2, Table.Column3, Table.Picture,
ISNULL(
CASE SUBSTRING(Table.ImageName,(LEN(Table.ImageName)-2),3)
WHEN 'jpg'
THEN 'image/jpeg'
ELSE 'image/' + SUBSTRING(Table.ImageName,LEN(Table.ImageName)-2,3)
END,'image/jpeg') as 'ImageExtension',
FROM Table

The CASE statement will give you the proper Image Extension based on the name of the Picture. If the Image column is NULL then it will provide a default MIMEType of 'image/jpeg' to avoid raising Errors - this is an important.

1.) Drag and drop Image to the Report Body.
2.) On the Image properties:
- set Value: =Fields!EmployeePicture.Value
- set MIMEType: =Fields!ImageExtenion.Value

Create Stored Procedure IF NOT EXISTS (SQL Server)

This is especially useful if you're writing update scripts like I am.

IF NOT EXISTS(Select * from sysobjects where name = 'usp_UserSproc')
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE usp_UserSproc
(
@ID int = NULL --nullable input
)
AS
BEGIN
SET NOCOUNT ON;
IF @ID IS NOT NULL
SELECT * FROM My_Table WHERE ID=@ID;
ELSE
SELECT * FROM My_Table WHERE ID=1;
END';
END