MSSQL Quick Reference

Snippets below have been condensed from their original sources for brevity. See references for original articles.

Great reference on joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Visual_SQL_JOINS_V2

Show all tables in specific database: (MSSQL equivalent of MySql “show tables”)

select name from <database name>..sysobjects where xtype = 'U'

Insert into:

INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists;

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists 
    WHERE MusicianID > 3;

INSERT INTO Residents (Name, Occupation)
    SELECT Name, Occupation FROM Immigration 
    WHERE Residency = 'granted';

INSERT INTO Insurance (Name) 
    SELECT Employee.Username FROM Employee 
    INNER JOIN Project ON Employee.EmployeeID = Project.EmployeeID 
    WHERE Project.ProjectName = 'Hardwork';

Insert if not exists:

IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom ANDApplication = Calculator) BEGIN
 INSERT INTO dbo.Applications 
 (Date, Username, Application, Version) 
 VALUES
 ('3/10/2009', 'Tom', 'Calculator', '2.0') 
END

Trigger Syntax Structure:

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS 
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ] 
[ ...n ] 
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
{ comparison_operator } column_bitmask [ ...n ] 
} ] 
sql_statement [ ...n ] 
} 
} 

Basic If, Then , Else and case:

--longer
create proc sp_generic (@cust int, @type int)
if @type = 1
 select * from customer where customerid = @cust
else
 Select * from deleted_customers where customerid = @cust

--shorter
create proc sp_generic (@deptid int )
select * from employees where departmentid = case when @dept >0 then @dept else departmentid end
order by departmentid

Insert Trigger:

CREATE TRIGGER trig_addAuthor 

ON authors 

FOR INSERT 

AS

-- Get the first and last name of new author 

DECLARE @newName VARCHAR(100) 

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

-- Print the name of the new author 

PRINT 'New author "' + @newName + '" added.'

Update Trigger:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER Person.reminder;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
-- Test the trigger.
UPDATE Person.Address
SET PostalCode = 99999
WHERE PostalCode = '12345';
GO

Get and/or compare today’s date:

convert(varchar,CURRENT_TIMESTAMP,101)
convert(varchar(12),getdate(),101)

Insert or Update with case statements:

USE [mydatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_manufacturedrobots_count]
	@mfgid int,
	@robotid int,
	@paintbotcount int=null,
	@drillbotcount int=null,
	@hammerbotcount int=null
AS
BEGIN

	SET NOCOUNT ON;

declare @id int
select @id = id from manufacturedrobots where mfgid=@mfgid and robotid=@robotid
if @id is not null
update manufacturedrobots set 
paintbotcount=case
when @paintbotcount is null then paintbotcount 
else @paintbotcount end, 
favorite=case
when @drillbotcount is null then drillbotcount 
else @drillbotcount end, 
checkedin=case
when @hammerbotcount is null then hammerbotcount 
else @hammerbotcount end
where id=@id
else
insert into manufacturedrobots (paintbotcount,drillbotcount,hammerbotcount) values  (@paintbotcount,@drillbotcount,@hammerbotcount)

END

Cursor:

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO

References:
MSDN, http://msdn.microsoft.com/en-us/library/ms187326.aspx
DevGuru, http://www.devguru.com/technologies/t-sql/7124.asp
DevArticleshttp://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/
MSDN, “How to debug stored procedures in Visual Studio .NET”, http://support.microsoft.com/kb/316549
CodeGuru, http://www.codeguru.com/forum/showthread.php?t=473102
DaniWeb, http://www.daniweb.com/forums/thread43719.html
SqlAuthority, http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/

About Ronnie Diaz

Ronnie Diaz is a software engineer and tech consultant. Ronnie started his career in front-end and back-end development for companies in ecommerce, service industries and remote education. This work transitioned from traditional desktop client-server applications through early cloud development. Software included human resource management and service technician workflows, online retail e-commerce and electronic ordering and fulfillment, IVR customer relational systems, and video streaming remote learning SCORM web applications. Hands on server experience and software performance optimization led to creation of a startup business focused on collocated data center services and continued experience with video streaming hardware and software. This led to a career in Amazon Prime Video where Ronnie is currently employed, building software and systems which stream live sports and events for millions of viewers around the world.

Posted on February 1, 2011, in Language References, Programming & Development and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: