41. Output과 Return 키워드

--[!] Output과Return 키워드

 

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

Create Table dbo.Products

(

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

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

       SellPrice Int Null                                     -- 판매가

)

Go

 

 

--[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

-- Set @RecordCount = 0    -> Output은Set키워드로초기화하지않는다. C#에서의Out과동일한역할을한다.

Exec UpdateSellPrice 1, @RecordCount Output

Select @RecordCount

Go

 

 

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

--[a]

Select Count(*) From Products;

 

--[b]

Create Proc GetProductCount

As

       Select Count(*) From Products;

Go

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

Go

 

--[c]

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

       Return @@RowCount

Go

Declare @RecordCount Int

Exec @RecordCount = UpdateSellPriceHalf 1

Select @RecordCount

Go