COALESCE - Returns the first non-NULL expression in its argument list. If all arguments are null, an exception is thrown.
DECLARE @BookId INT ... SELECT Title FROM Book WHERE BookId = COALESCE(@BookId, BookId)
Format money using CONVERT (2 decimal places, commas for thousands):
DECLARE @Price MONEY SET @Price=1206.2 SELECT CONVERT(VARCHAR, CAST(@Price AS MONEY), 1) -- output: 1,206.20
@@ROWCOUNT
-- Example #1 UPDATE Flags SET IsRead=1 WHERE DateIsRead IS NOT NULL PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' item(s) set as "Read"' -- Example #2 SELECT @CustomerType=CustomerType FROM Customer WHERE LOWER(CustomerName)='bruce' IF @@ROWCOUNT=0 SELECT @CustomerType=DefaultCustomerType FROM DefaultSettings
Different types of identity:
-- Example #1: DECLARE @ID INT INSERT INTO Customer VALUES('Customer #1') SET @ID = SCOPE_IDENTITY() -- get the automatically generated ID of the new customer record
Overriding the identity value:
SET IDENTITY_INSERT MyTable ON
NEWID() - Generates a GUID:
SELECT NEWID() -- result: C616F086-FC91-46B7-BBCF-2BCCE159E6A7