-- ********* DEMO TRANSACTIONS *********

----------------------- MySQL -----------------------
use databasename;

start transaction;
  -- do something
commit;

----------------------- MS SQL Server -----------------------
use databasename;
GO

begin transaction;
  -- do something
commit;


-- ********* DEMO VIEWS *********
----------------------- MySQL -----------------------
use databasename;

-- drop view if exists
drop view if exists v_my_view;

-- create view (must use keyword "AS")
create view v_my_view as
  select...

-- show view definition
describe v_my_view;

-- display query result set 
select * from v_my_view;

----------------------- MS SQL Server -----------------------
use databasename;
GO

-- drop view if exists
IF OBJECT_ID (N'dbo.v_my_view', N'V') IS NOT NULL
DROP VIEW dbo.v_my_view;
GO

-- create view (must use keyword "AS")
create view dbo.v_my_view as
 select...
go

-- display tables and views
SELECT * FROM information_schema.tables;
go

-- show view definition
Select VIEW_DEFINITION
From INFORMATION_SCHEMA.VIEWS;
go

-- display query result set 
select * from dbo.v_my_view;
go

-- ********* DEMO STORED PROCEDURES *********
----------------------- MySQL -----------------------
use databasename;

-- drop stored procedure if exists
drop procedure if exists AddRecord;

-- must change delimiter so that semicolons after each statement in body of routine are not interpreted by SQL parser to mean end of CREATE PROCEDURE statement
delimiter // -- must use space between delimiter and chosen character(s)

-- create stored procedure
-- NOTE: () characters still required w/o parameters
create procedure AddRecord()
begin
  -- do something
end // -- use new delimiter to end create procedure statement

-- change delimiter back
delimiter ;

-- call (invoke) stored procedure
call AddRecord();

----------------------- MS SQL Server -----------------------
use databasename;
GO

-- drop stored procedure if exists
IF OBJECT_ID('dbo.AddRecord') IS NOT NULL
  DROP PROCEDURE dbo.AddRecord;
GO

-- no need to change delimiter
-- (go command interpreted as end of CREATE PROCEDURE statement)

-- create stored procedure (must use keyword "AS")
CREATE PROCEDURE dbo.AddRecord AS
  -- do something
GO

-- call (invoke) stored procedure
EXEC dbo.AddRecord;
GO
