Showing posts with label valued function. Show all posts
Showing posts with label valued function. Show all posts

Friday, 19 July 2013

Create table valued function in SQL Server 2008

Example:      Table valued function.............

CREATE function [dbo].[sp_TotalPending](@contract_code nvarchar(150),@buySell nvarchar(50),@tablefetch nvarchar(150))
returns @resultTable table
(
 totalVolumn int
)
as
begin

declare @orgVol int
if(@tablefetch='ORDER_File')
begin
if(@buySell='1')
begin
set @orgVol=(select isnull(SUM(cast(Original_Volume as int)),0)as OriginalVolume from
ORDER_File where Contract_Code=@contract_code and Buy_Sell_Ind=@buySell)
goto lbllast
end

if(@buySell='2')
begin
set @orgVol=(select isnull(SUM(cast(Original_Volume as int)),0)as OriginalVolume from
ORDER_File where Contract_Code=@contract_code and Buy_Sell_Ind=@buySell)
goto lbllast
end
end


if(@tablefetch='Trade_file')
begin
if(@buySell='1')
begin
set @orgVol=(select isnull(SUM(cast(Trade_Qty as int)),0)as TradeVolume from
Trade_file where Contract_Code=@contract_code and Buy_Sell_Ind=@buySell)
goto lbllast
end

if(@buySell='2')
begin
set @orgVol=(select isnull(SUM(cast(Trade_Qty as int)),0)as TradeVolume from
Trade_file where Contract_Code=@contract_code and Buy_Sell_Ind=@buySell)
goto lbllast
end
end


lbllast:
begin
insert into @resultTable values(@orgVol)
return
end
end