ตอนที่ 1 : รู้จักและการเขียน Stored Procedure บน SQL Server (SQL Server : Stored Procedure)

CREATE TABLE [CUSTOMER](
	[CUSTOMER_ID] [varchar](4) NOT NULL,
	[NAME] [varchar](50) NULL,
	[EMAIL] [varchar](50) NULL,
	[COUNTRY_CODE] [varchar](2) NULL,
	[BUDGET] [decimal](18, 2) NULL,
	[USED] [decimal](18, 2) NULL,
 CONSTRAINT [PK2_CUSTOMER] PRIMARY KEY CLUSTERED 
(
	[CUSTOMER_ID] ASC
) ) ON [PRIMARY]

INSERT INTO CUSTOMER VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000, 600000);
INSERT INTO CUSTOMER VALUES ('C002', 'John  Smith', '[email protected]', 'UK', 2000000, 800000);
INSERT INTO CUSTOMER VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000, 600000);
INSERT INTO CUSTOMER VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000, 100000);

CREATE TABLE [COUNTRY](
	[COUNTRY_CODE] [varchar](2) NOT NULL,
	[COUNTRY_NAME] [varchar](100) NULL,
 CONSTRAINT [PK_COUNTRY] PRIMARY KEY CLUSTERED 
(
	[COUNTRY_CODE] ASC
)) ON [PRIMARY]

INSERT INTO COUNTRY VALUES ('TH', 'Thailand  ');
INSERT INTO COUNTRY VALUES ('UK', 'England');
INSERT INTO COUNTRY VALUES ('US', 'United States');

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

CREATE PROCEDURE myStoredProcedure
AS
	SELECT * FROM CUSTOMER
GO

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 11-Sep-15 3:29:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myStoredProcedure]
AS
	SELECT * FROM CUSTOMER

EXEC myStoredProcedure