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

No comments:

Post a Comment