IF OBJECT_ID('tempdb..#some_temp_name') IS NOT NULL
PRINT '#some_temp_name exists.'
ELSE
PRINT '#some_temp_name does not exist.'

The following SQL will report the name of the currently executing stored procedure

/* Begin */
PRINT ISNULL(OBJECT_NAME(@@PROCID), '<none>')
/* End */

To fetch this into a variable use :

/* Begin */
DECLARE @procedure_name VARCHAR(255)
SET @procedure_name = ISNULL(OBJECT_NAME(@@PROCID), '<none>')
/* End */

The key to this is @@PROCID, however, this returns NULL if not executing in a stored procedure, the ISNULL prevents a NULL value being used

select cast(convert(varchar, getdate(), 108) as datetime)

Note: Code 108 returns the time portion of a date in the format hh:mm:ss

 

More a note to myself for this age old problem when restoring SQL databases.

Run this against the database you have the orphaned user in and it will fix the record in the master database.

Username/Password is for the user you need to fix.

EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'password'

 

DECLARE @SearchType int
DECLARE @SearchText varchar(50)

SET @SearchType = 1
SET @SearchText = 'A'


SELECT
*
FROM
[TableNameGoesHere]
WHERE
(@SearchType=1 AND [Field1] LIKE @SearchText + '%')
OR
(@SearchType=2 AND [Field2] LIKE @SearchText + '%')
ORDER BY
CASE @SearchType
WHEN 1 THEN [Field1]
WHEN 2 THEN [Field2]
END

Bit of an un-usual one but if you want to return a result set in a random order then you can use this;

SELECT *
FROM table
ORDER BY NEWID()

NEWID creates a unique value every time it's executed, the values are non-consecutive.

Where you have multiple rows that you need to either display or report on as one single, concatenated field you can use the Coalesce function to merge them.

You can either do this in a stored procedure or create a function so it can be used inline in Select statements.

Below is an example of a function that will merge multiple policy numbers into one string containing them separated by commas.

CREATE FUNCTION dbo.fn_MergePolicyNums
(@HGNum as int)
RETURNS varchar(100) AS

BEGIN

declare @PolNums varchar(100) /* Holds multiple MEC numbers */

SELECT @PolNums = COALESCE (@PolNums + ', ' + PolicyNum, PolicyNum, @PolNums) FROM tblPolicy WHERE HGNum = @HGNum

RETURN ISNULL(@PolNums, '')
END

 

This article describes how to delete a single row, when it is found that row is duplicated

Set Rowcount 1
Delete From
Table
Where
ID = 999 (Or any field name/value combination that identifies the duplicate row)
Set Rowcount 0

This article describes how to identify duplicate records in a SQL table.

Use the Group By / Having combination in a Select statement

Select
ID
From
Table
Group By
ID
Having
Count(ID) > 1