MS-SQL 공부노트 WHERE절 매개변수

 

[목적]
MS-SQL상에서 매개변수를 이용해서 Where절을 넣어 select 문의 결과를 보는것.

[목적-상세]

1) VBA상에서 매개변수 하나를 만든다. + 동시에 VBA에서 MS-SQL에 있는 저장프로시저를 실행시킨다.

2) MS-SQL의 저장 프로시저에서 매개변수를 포함한 SELECT 문을 실행한다.

 

 

[테이블구성]

테스트를 위해서 이름이 "테이블2"인 테이블을 아래와 같이 구성했습니다.

 

 

[테스트1]

select * from 테이블2 

select문이 정상적으로 작동합니다.

[테스트2-1]

select * from 테이블2 where ID=1

조건이 있는 select문도 정상적으로 작동합니다.

[테스트2-2]

select * from 테이블2 where 이름 = '현대'

조건이 있는 select문도 정상적으로 작동합니다.

[테스트2-3]

select * from 테이블2 where 숫자 = 200

조건이 있는 select문도 정상적으로 작동합니다.

[테스트2-4]

declare @STR nchar(10)
set @STR = '삼성'

select * from 테이블2
where
이름 = @STR

매개변수 @STR 조건이 있는 select문도 정상적으로 작동합니다.

[1차 결론과 의문점]

- 1차결론 : MS-SQL 상에서 매개변수를 이용한 select문 실행 성공: 위와 같은 방식으로 사용할수 있다.

- 의문점: declare @STR nchar(10) 에서

nchar(10) 는 테이블 고유의 테이터 형식이다. 만약 일치하지 않는다면?

같은 텍스트 형식이지만 nchar(20) 같이 길이가 달라진다던가 또는

nvarchar(20) 와 같이 길이와 형식이 달라진다면?

 

[테스트 3-1] Declare 부분 수정

nchar(10) -> nchar(20) : 작동

[테스트 3-1] Declare 부분 수정

nchar(10) -> nvarchar(20) : 작동

[2차 결론과 의문점]

- 2차결론: text계열의 경우 데이터 형식이 변경되더라도 변경되는것이 오류의 원인이 되지 않는다.

- 의문점: 실제 값이 형식안에 미리 정해진 값( nchar(10) 에서 10 같은)을 벗어나지 않는다면 오류가 안일어난다고 예상되는데 값을 줄여서 한번 보자.

 

[테스트 3-2] Declare 부분 수정

nchar(10) -> nchar(1) : 오류

결과가 나오지 않는다.

[테스트4] 프로시저 생성

CREATE PROCEDURE [dbo].[테스트프로시저]
@DNAME VARCHAR(50)

AS
SELECT 이름,숫자
 FROM dbo.테이블2
 WHERE 이름 = @DNAME;

 

[테스트4-1] VBA 코드 작성

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConnect As String

Set cnn = New ADODB.Connection

cnn.ConnectionString = "주소"

cnn.Open

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdStoredProc
    .CommandText = "테스트프로시저"
    .Parameters("@DNAME") = "삼성"
End With

Set rst = cmd.Execute

Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing

 

[테스트 4-2] 폼의 버튼 하나에 클릭이벤트를 만들고 클릭을 해본다.

call ADODB_CONNECTION_테스트프로시저

[테스트 4-2 결과, 의문점, 다른시도]

결과 1) VBA상의 오류 없음

결과 2) 아래와 같은 메시지가 나왔음.

결과 2-1) 결과를 보는데 사용한 방법은 ssms상의 저장프로시저 우클릭후 프로시저 실행을 시켜봄.

 

- 의문점 : VBA상에서 버튼클릭순간 이미 프로시저는 실행됐다고 가정한다면, 내가 '결과 2-1)' 에서 한 행동은 파라미터 값이 없는 상태로 다시 실행시키게 되는 계기가 될수 있다고 생각함.

아래 메시지 부분에 빨간글씨로 expects parameter '@DNAME', which was NOT SUPPLIED.

 

그간 이 메시지가 반복해서 뜨는데 이유를 몰랐었음.

지금 천천히 체크해 내려가는데 아마도 위의 내용때문일거라는 생각을함.

 

- 다른 시도: 결과값을 볼수 있는 프로시저를 MSSQL상에서 만들 방법을 찾아봐야함

 

 

[테스트 5 결과, 해결]

- 시도: 커서를 이용해서 원하는 결과를 볼수 있게 만들어 놓고 빈테이블을 확인해봄.

- 결론 1: VBA부분에서는 손댈게 없음

- 결론 2: 아래의 코드로 수정

 

USE [서버명]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[테스트프로시저]
@DNAME NCHAR(10)

AS
BEGIN
 DELETE FROM 테이블3

DECLARE @이름 Nchar(10)
, @숫자 int

DECLARE A_Cur CURSOR FOR 

SELECT 이름,숫자
  FROM dbo.테이블2
 WHERE 이름 = @DNAME;

 OPEN A_Cur

FETCH NEXT FROM A_Cur INTO @이름, @숫자

WHILE (@@FETCH_STATUS = 0 )

BEGIN

INSERT INTO 테이블3(이름,숫자) 
values(@이름,@숫자)

FETCH NEXT FROM A_Cur INTO @이름,@숫자
END

CLOSE A_Cur
DEALLOCATE A_Cur

end

 

결과이미지

새로만든 테이블3

 

[총평]

[4-2]의 내용과 같이

- 의문점 : VBA상에서 버튼클릭순간 이미 프로시저는 실행됐다고 가정한다면, 내가 '결과 2-1)' 에서 한 행동은 파라미터 값이 없는 상태로 다시 실행시키게 되는 계기가 될수 있다고 생각함.

아래 메시지 부분에 빨간글씨로 expects parameter '@DNAME', which was NOT SUPPLIED.

 

그간 이 메시지가 반복해서 뜨는데 이유를 몰랐었음.

지금 천천히 체크해 내려가는데 아마도 위의 내용때문일거라는 생각을함.

 

- 다른 시도: 결과값을 볼수 있는 프로시저를 MSSQL상에서 만들 방법을 찾아봐야함

 

실행을 두번한게 문제가 됐었는데, 그 부분을 인식하지 못하고 넘기는 방법이 뭐가 있는지만 찾았다는게 문제였음.