'ms-sql' 카테고리의 글 목록

DECLARE @old_datefirst INT

SELECT @old_datefirst = @@DATEFIRST

DECLARE @v_dayname_monday INT

SELECT @v_dayname_monday = 1

SET DATEFIRST @v_dayname_monday

DECLARE @v_date DATETIME

SELECT @v_date = '2009-08-01' ---특정 날짜 지정.

SELECT @v_date date

, DATEPART(year, @v_date) [Year]

, DATEPART(week, @v_date) [Week of Year]

, DATEPART(dayofyear, @v_date) [Day of Year]

, CASE

WHEN DATEPART(weekday, @v_date) > 1 THEN DATEADD(day, (DATEPART(weekday, @v_date)-1) * -1 , @v_date)

ELSE @v_date END [First-day of Week]

, CASE

WHEN DATEPART(weekday, @v_date) < 7 THEN DATEADD(day, 7-DATEPART(weekday, @v_date), @v_date)

ELSE @v_date END [Last-day of Week]

SET DATEFIRST @old_datefirst

/*

실행결과

date Year Week of Year Day of Year First-day of Week Last-day of Week

2009-08-01 2009 31 213 2009-07-27 2009-08-02

*/

- 날짜 변환 -

의미	PARAMETER	QUERY문
mon dd yyyy hh:mmAM (or PM)	100	select convert(char, getdate(), 100)
mm/dd/yyyy	101	select convert(char, getdate(), 101)
yyyy.mm.dd	102	select convert(char, getdate(), 102)
dd/mm/yyyy	103	select convert(char, getdate(), 103)
dd.mm.yyyy	104	select convert(char, getdate(), 104)
dd-mm-yyyy	105	select convert(char, getdate(), 105)
dd mon yyyy	106	select convert(char, getdate(), 106)
mon dd, yyyy	107	select convert(char, getdate(), 107)
hh:mm:ss	108	select convert(char, getdate(), 108)
mon dd yyyy hh:mm:ss:mmmAM (or PM)	109	select convert(char, getdate(), 109)
mm-dd-yyyy	110	select convert(char, getdate(), 110)
yyyy/mm/dd	111	select convert(char, getdate(), 111)
yyyymmdd	112	select convert(char, getdate(), 112)
dd mon yyyy hh:mm:ss:mmm	113	select convert(char, getdate(), 113)
hh:mm:ss:mmm(24h)	114	select convert(char, getdate(), 114)
yyyy-mm-dd hh:mm:ss(24h)	120	select convert(char, getdate(), 120)
yyyy-mm-dd hh:mm:ss.mmm	121	select convert(char, getdate(), 121)

대표적으로 많이 쓰는 얘들이 위와 같다.

/*
-- 해당 주의 시작일과 종료일 구하는 방법
*/
DECLARE @NOWDATE DATETIME
SELECT @NOWDATE = CONVERT(VARCHAR, GETDATE(), 23) --GETDATE()
--지난주를 구하는 경우
--SELECT @NOWDATE = CONVERT(VARCHAR, DATEADD(WW, -1,GETDATE()), 23)

DECLARE @WYEAR INT -- 해당 년도
DECLARE @WWEEK INT -- 해당 주차
DECLARE @SWEEK DATETIME -- 해당주 시작일
DECLARE @EWEEK DATETIME -- 해당주 종료일

SELECT
  @WYEAR = DATEPART(year, @NOWDATE)
, @WWEEK = DATEPART(week, @NOWDATE)
, @SWEEK =
  (CASE
   WHEN DATEPART(weekday, @NOWDATE) > 1 THEN DATEADD(day, (DATEPART(weekday, @NOWDATE)) * -1 , @NOWDATE)
   ELSE @NOWDATE END
  )
, @EWEEK =
  (CASE
   WHEN DATEPART(weekday, @NOWDATE) < 7 THEN DATEADD(day, 7-DATEPART(weekday, @NOWDATE), @NOWDATE)
   ELSE @NOWDATE END
  )

select @NOWDATE Date , @WYEAR Year, @WWEEK Week, @SWEEK Week_Start, @EWEEK Week_End

/*
-- varchar(14)인 경우
DECLARE @VNOWDATE VARCHAR(14)
DECLARE @VSWEEK VARCHAR(14)
DECLARE @VEWEEK VARCHAR(14)
SET @VNOWDATE = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @NOWDATE, 20),'-',''),':',''),' ','')
SET @VSWEEK = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @SWEEK, 20),'-',''),':',''),' ','')
SET @VEWEEK = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @EWEEK, 20),'-',''),':',''),' ','')
SELECT @VNOWDATE Date, @WYEAR Year, @WWEEK Week, @VSWEEK Week_Start, @VEWEEK Week_End
-- //varchar(14)인 경우
*/

LIST

[스토어드 프로시저] @@error 와 @@rowcount 를 이용한 에러 처리

[DB] MsSql

2010/04/12 01:48

http://blog.naver.com/mrgoguma/140104872841

정확한 코드는 다음과 같습니다.

declare @result_code int , @processed_rows int

UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX'

select @result_code = @@error , @processed_rows = @@rowcount

if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end

select 문도 변수에 값을 저장할 수 있습니다.

자주 저지르는 실수!

declare @result_code int , @processed_rows int

UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX' -- 'AX'라는 부서는 존재하지 않는다고 가정. 이럴 경우 처리된 행의 개수는 0개임

set @processed_rows = @@rowcount
set @result_code = @@error

if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end

위의 코드는 에러가 발생하면 @@error 와 @@rowcount 를 별도의 변수에 저장하고 에러를 처리하는 코드입니다. 그런데 위의 에러처리 루틴은 결코 실행되지 않습니다. @result_code 를 세팅하기 전에 set @processed_rows = @@rowcount 가 성공하면서 @@error 가 0으로 되고 @result_code 에는 언제나 0 이 세팅되기 때문입니다.

그렇다면 코드를 이렇게 바꾸면 어떨까요?

declare @result_code int , @processed_rows int

UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX'

set @result_code = @@error
set @processed_rows = @@rowcount

if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end

update 문을 실행하자마자 @@error 를 @result_code 에 넣었으므로 정상적인 값이 들어갈 것입니다. 그러면 @@rowcount 는 어떨까요? DEPT 테이블에 AX라는 부서가 없으므로 @@rowcount 는 0 이 되어야 합니다. 하지만 실행시켜보면 @processed_rows 에 1 이 들어가 있습니다. 이게 어찌된 일일까요???

원인은 set @result_code = @@error 때문입니다. @@rowcount 는 set 문에 의해서도 영향을 받습니다. 따라서 set @result_code = @@error 가 성공하면 한 개의 데이터가 변수에 저장되었으므로 @@rowcount 는 1이 되는 것입니다.

[출처] Stored Procedure 작성시 에러 처리 팁

2. rollback, commit 할 때는 언제나 @@trancount 를 체크하자

@@trancount : 트랜잭션의 중첩 수준을 나타내는 시스템 함수. Begin tran 문에 의해 1씩 증가하고 commit tran 에 의해 1씩 감소한다. rollback tran 을 만나면 모두 롤백하면서 어떤 값을 가지고 있더라도 0 으로 변한다.

sp를 작성할 때 데이터의 정합성을 보장하기 위해서 트랜잭션을 사용하게 됩니다. 트랜잭션을 시작하면 언제나 begin tran, commit tran의 짝이 일치해야 하는 것은 당연하지요. 즉, 트랜잭션이 중첩되어서 begin tran 을 세번하게 되면 세번 commit 이 있어야 합니다. 단 rollback 은 한번에 @@trancount 를 0 으로 만들고 rollback 하기 때문에 rollback 하기 전에 언제나 @@trancount 를 체크하면서 rollback 해야 합니다.

begin tran

INSERT INTO DEPT ( … )

if @@error <> 0 or @@rowcount <> 1
begin
if @@trancount > 0 rollback
select -1 as err_code
return
end

INSERT INTO TEST_TBL ( … )

if @@error <> 0 or @@rowcount <> 1
begin
if @@trancount > 0 rollback
select -2 as err_code
return
end

if @@trancount > 0 commit

rollback 이나 commit 하기 전에 @@trancount 를 체크하는 것은 sp들간에 복잡하게 호출하고 각 sp들이 자체적으로 transaction 을 사용할 때 위력을 발휘합니다. 중첩 트랜잭션 처리에 관한 사항은 기배포한 중첩 트랜잭션 처리 가이드를 참고해주세요.

[출처] [스토어드 프로시저] @@error 와 @@rowcount 를 이용한 에러 처리 |작성자 고구마

LIST

SQL Server Management Studio를 실행하자.

그러면 서버에 연결 대화상자가 나타난다. 일단 인증 항목에서 Windows 인증과 SQL Server 인증이 있는데 SQL Server 인증을 사용하기위해 (만약 SQL Server 인증 사용이 설정되어 있지 않다면...) 설정하는 것 부터 시작하자. Windows 인증에 연결 버튼을 클릭하자.

서버 항목에 (1번 항목) 마우스 오른쪽 버튼을 누르고 메뉴 항목에서 2번 항목에서 속성을 클릭한다. 그러면 서버 속성 대화상자가 나타난다.

여기서 서버 인증 항목에서 SQL Server 및 Windows 인증 모드를 선택하고 확인을 누르자. 그러면 다음과 같이 대화상자가 나타난다.

확인을 클릭하자. 일단 서버를 다시 시작하기전에 SQL Server 인증으로 로그인 할 수 있도록 sa 로그인을 가능하도록 하자.

게체 탐색기에서 보안->로그인->sa 항목을 마우스 오른쪽 클릭하여 메뉴에서 속성을 클릭하자. 그러면 로그인 속성 - sa 대화상자가 나타난다.

만약 암호를 다시 설정하고 싶으면 1번 항목을 선택하고 2번 항목에서 암호를 다시 입력하자. 다음에 SQL Server 인증으로 로그인이 되도록 설정하자.

항목에서 1번 상태 항목을 선택하고 2번, 3번을 선택한다. 그리고 확인을 누르자.

1번 데이터베이스 항목을 마우스 오른쪽 버튼 클릭하여 메뉴에서 2번 항목 다시 시작을 선택하자.

그리고 나타난 대화상자에서 예를 선택하자. 그렇면 데이터메이스가 다시 시작된다. 자 그렇면 새로운 데이터베이스 QuadraThreeDMap을 생성해보자.

개체 탐색기에서 데이터베이스를 마우스 오른쪽 버튼을 눌러 메뉴에서 항목에서 새 데이터베이스를 선택하자. 그렇면 새 데이터베이스 대화상자가 나타난다.

일단 1번 일반 항목에서 2번 데이터베이스 이름에 QuadraThreeDMap을 입력하여 확인 버튼을 누르자. 그러면 다음과 같이 데이터베이스가 추가된다.

자 인제 Quadra 사용자를 추가하자.

보안 항목에서 로그인 항목에 마우스 오른쪽 클릭해서 메뉴에서 새 로그인 항목을 선택한다. 그러면 로그인 - 신규 대화상자가 나타난다.

대화상자에서 일반 항목을 선택한후, 로그인 이름을 Quadra라고 입력하고, 3번 처럼 SQL Server 인증을 선택한 후 암호을 입력하고 편의를 위해 암호 만료 강제 적용 체크를 없애자. 또, 4번 항목에서 기본 데이터베이스와 기본 언어를 각각 QuadraThreeDMap과 Korean으로 하자.

다음에 사용자 매핑 항목을 선택하고, 2번 항목처럼 db_owner를 선택하고 3번 처럼 체크를 하고 확인을 누른다. 자 사용자 추가와 데이터베이스 추가를 마쳤다. 마지막으로 사용자에게 권한을 주어야 한다.

데이터베이스 속성에서 사용 권한을 위와 같이 주면 된다. 인제 모두 마쳤으므로, 확인을 위해 다시 데이터베이스 연결을 해보자.

파일 메뉴에서 개체 탐색기 연결...을 선택하자.

서버에 연결 대화상자에서 인증, 로그인, 암호를 입력하고 연결 버튼을 누르자.

끝... ^^ KIN

LIST

색인의 사용
    장점
    1. 데이터를 찾을(SELECT) 때 빨리 찾기 위해서 사용한다.
       (색인이 없다면 특정한 값을 찾기 위해 모든 데이터를 뒤진다 - Table Scan,
        모든 페이지를 뒤지지 않고 색인 페이지를 찾아서 쉽게 데이터를 가져온다 - Index Seek)
    2. 특정한 작업을 빨리 할 수 있다. (데이터 정렬(ORDER BY), 그룹단위 계산함수(GROUP BY))
    3. 유일성(uniqueness) 검사를 할 수 있다. 즉, 키 값이 같은 행이 두번 입력되지 않도록 하기 위해서도 색인을 사용한다.
    단점
    1. 만드는데 시간이 걸린다.
    2. 만드는데 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요하다.
    3. 데이터 수정(INSERT, UPDATE, DELETE)하는 시간, 특히 INSERT 작업은 오히려 더 많이 걸린다.

색인을 사용하는 컬럼의 기준
    1. WHERE 절에서 자주 사용되는 컬럼
    2. 자주 검색되는 컬럼(SELECT 절에서)
    3. 기본 키(자동적으로 색인이 만들어진다.)
    4. 참조키(foreign key): 조인 사용할 때에 조인의 속도를 향상시킨다. 색인이 자동적으로 만들어지지 않는다.

LIST

iTouch - Programing story

ms-sql

ms-sql 해당주의 시작일과 종료일 구하는 방법

[스토어드 프로시저] @@error 와 @@rowcount 를 이용한 에러 처리 [DB] MsSql

SQLServer2005 사용자 생성 및 DB 생성

index

+ Recent posts

티스토리툴바