MSSQL 프로시저 작성방법

초징 2017. 12. 13. 14:54

2017. 12. 13. 14:54

MSSQL 프로시저 작성 방법

1. Select 하는법

기존에 있던 프로시저 중 하나를 가져와 보면 다음과 같다.

"OrgGet"은 프로시저 이름이다.

USE [Unstoring]

/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후 1:49:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[OrgGet]

BEGIN

SELECT * From organization;

END

보통

USE [Unstoring]

/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후 1:49:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

까지는 자동으로 써진다. 지금 ALTER 이라고 써져있는 이유는 이미 만들어져있는 프로시저에서 수정하기 를 눌러 쿼리가 생성되어있기 때문에 ALTER라고 씌여져 있는 것이다. 처음 만들 때는 ALTER가 아닌 CREATE 로 만들어야 한다.

그렇기 때문에, 프로시저를 만들려고 하면 다음과 같이 입력하고 실행해야 맞는 이야기다.

USE [Unstoring]

/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후 1:49:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROC [dbo].[OrgGet]

BEGIN

SELECT * From organization;

END

이렇게 해야 되는데 여기서 바뀐 것은 ALTER에서 CREATE밖에 없다. 이렇게 작성한 뒤, “실행”버튼을 누르거나 F5키를 누르면 프로시저가 생성된다. 한번 생성한 프로시저는 지울때까지 사라지지 않는다. 이 프로시저를 수정하는 방법은 위에서 보았듯이 ALTER를 사용하여 수정하면 된다. 이제 프로시저를 개발툴에서 가져다가 쓸 수 있게 되었다. 개발툴에서 쓰는 방법은 좀 뒤에 설명하겠다.

2. Update하는법

USE [Unstoring]

/****** Object: StoredProcedure [dbo].[ProductAdd] Script Date: 2017-12-13 오후 2:00:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[ProductAdd]

(

@code NVARCHAR(50),

@name NVARCHAR(50),

@consumer MONEY,

@prevailing MONEY,

@agency MONEY,

@cooperative MONEY

)

DECLARE

@MSG VARCHAR(100)

BEGIN TRY

BEGIN

INSERT INTO product (prdt_code,prdt_name,consumer_price,prevailing_price,agency_price,cooperative_price)

VALUES (@code,@name,@consumer,@prevailing,@agency,@cooperative);

SET @MSG = 'Product_Input_Success';

END

END TRY

BEGIN CATCH

BEGIN

SET @MSG = 'Product_Input_Fail';

END

END CATCH

SELECT @MSG AS MSG;

ProductAdd는 프로시저 이름이다.

@변수를 하게되면 개발툴에서 넘겨주는 파라미터를 이용하여 값을 가져와 쓸 수 있다. 반드시 개발툴에서 넘겨지는 파라미터와 @변수 개수가 똑같아야 하며 타입도 똑같아야 한다. 순서는 반드시 똑같게 할 필요는 없다. 하지만 파라미터와 @변수는 파라미터 = 변수 로 같아야 한다. 또, @변수를 선언하게 되면 반드시 개발툴에서 파라미터를 만들어 입력을 꼭 해줘야 한다. 그러니까 예를 들면, @변수가 @test 였다고 하면, 개발툴에서의 파라미터 이름은 반드시 test로 해야 프로시저가 알아듣는다는 이야기이다.

Declare는 내가 프로시저 안에서 쓸 변수 같은 지역변수 같은 느낌으로 보면 된다. 개발툴에서는 접근할 수 없는 변수이고 또 접근할 필요가 없는 변수이면 Declare를 쓰는 게 낫다. 무작정 @변수로 만들어버리면 개발툴에서 반드시 파라미터로 값을 입력해줘야하는 번거로움이 생기기 때문이다. 나 같은 경우는 @MSG 라는 Declare 변수를 만들어서 INSERT가 정상적으로 처리가 되었는지 확인하는 용도로 썼다.

BEGIN TRY / END TRY 는 Try/Catch 문이라고 이해하면 쉽다. BEGIN TRY 문 안에서 에러가 났으면 중지하고 BEGIN CATCH문으로 들어가게 된다. 보통 정상적으로 프로시저가 실행이 되었는지 확인하는 용도로 쓴다고 생각하면 된다.

BEGIN / END 는 그냥 단순히 BEGIN 은 { , END는 } 라고 생각하면 된다고 구글신이 그랬다.

3. 개발툴에서 Select 하는 법

string strCon = "server = " + serverIP + "," + serverPort + "; uid =id; pwd = password!; database = dbname; Connection Timeout = 5"; //MSSQL 연결하기 위한 작업

string m_sqlCon = new SqlConnection(strCon); // 명령어 생성

SqlCommand scom = new SqlCommand("OrgGet", m_sqlCon); // "OrgGet"은 프로시저 이름이다. 프로시저 명령어 생성부분

if (m_sqlCon.State == ConnectionState.Closed) //DB가 닫혀있다면 열기

m_sqlCon.Open();

scom.CommandType = CommandType.StoredProcedure; //프로시저 타입 지정

if (_param != null) // 파라미터가 있을 때 파라미터 값들 추가하는 부분

scom.Parameters.AddRange(_param); // Select는 파라미터 추가가 없으니 건너뛰게됌

DataSet dataset = new DataSet();//데이터셋 생성

using (SqlDataAdapter da = new SqlDataAdapter(scom)) // 어댑터를 이용하여 데이터 받아오기

{

da.Fill(dataset); // 데이터 채우기 dataset에 정보가 담겨져있음.

}

m_sqlCon.Close(); //디비 닫기

4. 개발툴에서 Update 하기

string strCon = "server = " + serverIP + "," + serverPort + "; uid = id; pwd = password; database = dbname; Connection Timeout = 5"; //MSSQL 연결하기 위한 작업

string m_sqlCon = new SqlConnection(strCon); // 명령어 생성

SqlCommand scom = new SqlCommand("ProductAdd", m_sqlCon); //프로시저 생성. 이름 주의해서 써야함. 파라미터는(이름,연결명령어)

var _param = new[]{ //파라미터 생성

new SqlParameter {

ParameterName = "@code", //위의 @변수 이름 그대로 생성