MSSQL 프로시저 작성 방법
1. Select
하는법
기존에 있던 프로시저 중 하나를 가져와 보면 다음과 같다.
"OrgGet"은 프로시저 이름이다.
USE [Unstoring]
GO
/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후
1:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[OrgGet]
AS
BEGIN
SELECT * From organization;
END
보통
USE [Unstoring]
GO
/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후
1:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
까지는 자동으로 써진다. 지금 ALTER
이라고 써져있는 이유는 이미 만들어져있는 프로시저에서 수정하기 를 눌러 쿼리가 생성되어있기 때문에 ALTER라고
씌여져 있는 것이다. 처음 만들 때는 ALTER가 아닌 CREATE 로 만들어야 한다.
그렇기 때문에, 프로시저를 만들려고 하면 다음과 같이 입력하고 실행해야
맞는 이야기다.
USE [Unstoring]
GO
/****** Object: StoredProcedure [dbo].[OrgGet] Script Date: 2017-12-13 오후
1:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[OrgGet]
AS
BEGIN
SELECT * From organization;
END
이렇게 해야 되는데 여기서 바뀐 것은 ALTER에서 CREATE밖에 없다. 이렇게 작성한 뒤, “실행”버튼을 누르거나 F5키를
누르면 프로시저가 생성된다. 한번 생성한 프로시저는 지울때까지 사라지지 않는다. 이 프로시저를 수정하는 방법은 위에서 보았듯이 ALTER를 사용하여
수정하면 된다. 이제 프로시저를 개발툴에서 가져다가 쓸 수 있게 되었다. 개발툴에서 쓰는 방법은 좀 뒤에 설명하겠다.
2.
Update하는법
USE [Unstoring]
GO
/****** Object: StoredProcedure [dbo].[ProductAdd] Script Date: 2017-12-13 오후
2:00:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ProductAdd]
(
@code NVARCHAR(50),
@name NVARCHAR(50),
@consumer MONEY,
@prevailing MONEY,
@agency MONEY,
@cooperative MONEY
)
AS
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", //위의 @변수 이름 그대로 생성
Direction
= ParameterDirection.Input,
Value
= info.strPrdtCode //Value타입은 위의 프로시저 그대로 입력해야함.
//예를들면
int는 반드시 int타입으로 입력
},
new
SqlParameter {
ParameterName
= "@name",
Direction
= ParameterDirection.Input,
Value
= info.strPrdtName
},
new
SqlParameter {
ParameterName
= "@consumer",
Direction
= ParameterDirection.Input,
Value
= info.strConsumerPrice
},
new
SqlParameter {
ParameterName
= "@prevailing",
Direction
= ParameterDirection.Input,
Value
= info.strPrevailingPrice
},
new
SqlParameter {
ParameterName
= "@agency",
Direction
= ParameterDirection.Input,
Value
= info.strAgencyPrice
},
new
SqlParameter {
ParameterName
= "@cooperative",
Direction
= ParameterDirection.Input,
Value
= info.strCooperativePrice
}
}; //@변수를 하나라도 빼뜨리고 파라미터로 넘겨주면 안됨. 변수 있는거 모두 값을 지정하여 넘겨줘야함
if (m_sqlCon.State ==
ConnectionState.Closed)
m_sqlCon.Open();
scom.CommandType =
CommandType.StoredProcedure;
if (_param != null)
scom.Parameters.AddRange(_param);
DataSet dataset = new DataSet();
string str = "";
using (SqlDataAdapter da = new
SqlDataAdapter(scom))
{
da.Fill(dataset);
str
= dataset.Tables[0].Rows[0].ItemArray[0].ToString();
//@MSG를 보기위해 만든 것. 성공인지 실패인지 가리기 위해 가져온 부분. 테이블이 Select가 1개밖에 실행되지 않으니 Table의 Rows는 0번째일것이다. 그리고 쭈루룩 들어오는 아이템들은 ItemArray로 들어오게 된다. MSG는 하나의 줄만 들어올 테니 ItemArray에는 0번째 아이템이 들어오겠지 그게 Success 인지 Fail인지 들어오게 되니 그걸 확인하기 위하여 위와 같은 dataset.Tables[0].Rows[0].ItemArray[0].ToString();를
사용하여 확인하는 것이다.
}
m_sqlCon.Close();
이거에서 응용하면 된다!
