Create Stored Procedure using Case and Conditional Satatment in SQL Server 2008

Example:............for Stored Procedure...

Create proc [dbo].[sp_Order_Confirm]        
@Buy_Sell_Ind INT,        
@orderId NUMERIC(10,0),        
@price NUMERIC(9,2)=0,        
@code nCHAR(20),        
@cwhere VARCHAR(200)='',
@OptionType nchar(1)=''        
AS        
BEGIN        
 if @cwhere='RL'      
 begin      
 declare @sPrice numeric(9,2)    
 declare @bPrice numeric(9,2)  
 declare @ltp numeric(9,2)
 -------------------------------when price between Buy sell
   IF EXISTS(SELECT * FROM dbo.Product_Det WHERE ScripCode=@code AND @Buy_Sell_Ind=1 AND (convert(numeric(9,2),SellPrice)= convert(numeric(9,2),@price)OR convert(numeric(9,2),LastTradedPrice)= convert(numeric(9,2),@price))  OR ScripCode=@code AND @Buy_Sell_Ind=2 AND (convert(numeric(9,2),BuyPrice) = convert(numeric(9,2),@price)OR convert(numeric(9,2),LastTradedPrice) = convert(numeric(9,2),@price)) )        
   BEGIN      
   UPDATE dbo.ORDER_File SET confirmed='1' WHERE Order_Number=@orderId        
   END        
     -----------------------------------  end beween price
     
     ---------------------------------------------- buy confirmed when  price greater than sell price
     ----------------------------------------------sell confirmed when prive smaller than Buy price
       IF EXISTS(SELECT * FROM dbo.Product_Det WHERE ScripCode=@code AND @Buy_Sell_Ind=1 AND (convert(numeric(9,2),SellPrice) < convert(numeric(9,2),@price)OR convert(numeric(9,2),LastTradedPrice) < convert(numeric(9,2),@price)OR convert(numeric(9,2),@price)=convert(numeric(9,2),0))
       OR ScripCode=@code AND @Buy_Sell_Ind=2 AND (convert(numeric(9,2),BuyPrice) > convert(numeric(9,2),@price) OR convert(numeric(9,2),LastTradedPrice) > convert(numeric(9,2),@price) ))        
   BEGIN          
    SELECT @sPrice=CONVERT(NUMERIC(9,2),sellprice),@bPrice=CONVERT(NUMERIC(9,2),buyprice),@ltp=convert(numeric(9,2),LastTradedPrice) FROM dbo.Product_Det WHERE ScripCode=@code AND @Buy_Sell_Ind=1  OR ScripCode=@code AND @Buy_Sell_Ind=2    
 
    UPDATE dbo.ORDER_File SET confirmed='1' ,    
          price=case when ( @Buy_Sell_Ind=1 and @OptionType='') then @sPrice
          when (@Buy_Sell_Ind=1 and @OptionType='4') then @price    
          when (@Buy_Sell_Ind=2 and @OptionType='')  then @bPrice
          when (@Buy_Sell_Ind=2 and @OptionType='5') then  @price
      end    
     WHERE Order_Number=@orderId        
     END  
     --------------------------------  end smaller n greater than condition
     -------------------------------end smaller n greater than condition
       
    end    ---RL end  
   
   
   ------------------------trigger price condition
         
    else if @cwhere='SL'    
   begin      
    IF EXISTS(SELECT * FROM dbo.Product_Det WHERE ScripCode=@code AND @Buy_Sell_Ind=1 AND convert(numeric(9,2),SellPrice)=convert(numeric(9,2),@price) OR ScripCode=@code AND @Buy_Sell_Ind=2 AND convert(numeric(9,2),BuyPrice) = convert(numeric(9,2),@price)
)        
    BEGIN        
      UPDATE dbo.ORDER_File SET confirmed='1' WHERE Order_Number=@orderId        
    END      
 end      --end trigger
      ---------------------------------------------------------------  
END

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

Create Trigger after insert into table in SQL Server 2008

/****** Example of trigger on table after insert ******/

CEATE TRIGGER [dbo].[MarginVal]
   ON   [dbo].[Trade_file]
   AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
-- INTERFERING WITH SELECT STATEMENTS.
SET NOCOUNT ON;
DECLARE @SYSMBOL VARCHAR(200)
DECLARE @EXPIRY VARCHAR(200)
DECLARE @TRADENO VARCHAR(200)
DECLARE @BUYSELL VARCHAR(10)
DECLARE @PRICE DECIMAL(18,2)
DECLARE @LOT VARCHAR(18)
DECLARE @USERID VARCHAR(200)
declare @Trade_Number varchar(200)
------------------------------------------LAST INSETED DATA
SELECT @Trade_Number=Trade_Number,@USERID=USER_ID,@SYSMBOL=CONTRACT_DESCRIPTION,@EXPIRY=EXPIRY_DATE,@TRADENO=TRADE_NUMBER,@BUYSELL=BUY_SELL_IND,@PRICE=PRICE,@LOT=TRADE_QTY FROM INSERTED
-------------------------------------------------------------
------------------------------------ MARGIN FILE  FIND DAILY MARGIN
DECLARE @TMARGIN VARCHAR(200)
SELECT @TMARGIN=TOTALMARGIN FROM MARGIN_FILE WHERE SYMBOL=@SYSMBOL AND CONVERT(DATE,EXPIRY,107)=CONVERT(DATE,@EXPIRY,107)
------------------------------------------------------------------------
------------------------------------FIND LOT SIZE ACCORING TO SYSBMOL AND EXP
DECLARE @PRICEQTN VARCHAR(50)
DECLARE @LOTSIZE NUMERIC(18,2)
SELECT @PRICEQTN=PRICEQTN, @LOTSIZE=LOTSIZE FROM COMMLOTSIZE WHERE COMMODITY=@SYSMBOL

-------------------------------------------------------------------------------SELECT DATA AFTER THEN INSERT IN TO MARGIN TABEL
INSERT INTO CAL_MARGIN (USER_ID, Trade_Number,COMMODITY, EXPIRY, LTP, PRICEQTN, LOTSIZE, MARGIN, LOTVALUE, APPXMARGIN)
SELECT @USERID AS USERID,@Trade_Number,@SYSMBOL AS SYMBOL,@EXPIRY AS EXPIRY,@PRICE AS LTP,@PRICEQTN AS PRICEQTN,@LOTSIZE AS LOTSIZE,@TMARGIN AS MARGIN,
CONVERT(NUMERIC(18,2),@PRICE*@LOTSIZE) AS  LOTVALUE, CONVERT(NUMERIC(18,2),@PRICE*@LOTSIZE*@TMARGIN*CONVERT(INT,RTRIM(@LOT))/100) AS APPX_MARGIN

--SELECT * FROM TRADE_FILE
   
END