43.MS_SQL 2008 - Output과 Return 키워드

--[!] Output과Return 키워드

--[1] 샘플테이블생성

Create Table dbo.Products

(

       ProductID Int Identity(1, 1) Primary Key, --일련번호

       ModelName VarChar(25) Not Null, --상품명

       SellPrice Int Null --판매가

)

 

--[2] 예시문입력

Insert Into Products Values('좋은책', 5000);

Insert Into Products Values('좋은컴퓨터', 10000);

Insert Into Products Values('좋은냉장고', 9000);

 

--[3] 상품의가격을2배로업데이트, 업데이트된레코드의개수를반환

Create Proc UpdateSellPrice

       @ProductID Int,

       @RecordCount Int OUTPUT --결과값을리턴, 초기화하지않은채변수값만전달

As

       Update Products Set SellPrice = SellPrice * 2 Where ProductID = @ProductID

       Set @RecordCount = (Select @@ROWCOUNT)

            --현재프로시저내에서업데이트된레코드개수

Go

 

Exec UpdateSellPrice 1, 1

Go

 

Select *From Products

Go

Declare @RecordCount Int

Exec UpdateSellPrice 1, @RecordCount Output

Select @RecordCount

Go

 

--[4] Products 테이블에있는모든레코드의개수반환

Select COUNT(*) From Products;

Create Proc GetProductCount

As

       Select COUNT(*) From Products;

Go

Execute GetProductCount --결과값을레코드셋, 스칼라값(집계함수)

Go

 

Create Proc GetProductCountUp

       @RecordCount Int Output

As

       Select @RecordCount = COUNT(*) From Products;

Go

Declare @RecordCount Int

Exec GetProductCountUp @RecordCount Output

Select @RecordCount

Go

 

--[5] 상품의가격을반값으로조정한후영향받은레코드수반환(Return)

Create Proc UpdateSellPriceHalf

       @ProductID Int

As

       Update Products Set SellPrice = SellPrice / 2

       Where ProductID > ProductID

      

       --Select @@RowCount

Go

Declare @RecordCount Int

Exec @RecordCount = UpdateSellPriceHalf 1

Select @RecordCount

Go

---

43Output&Return.sql