You must Sign In to post a response.
  • Category: SQL Server

    How to use views inside the stored procedure

    I have created views,Views as follows

    select cr.stud_id, stud_Name = (select stud_name from student where stud_id = cr.stud_id),
    cbm.cmn_minor_code as Course,
    RegType = case when isnull(cr.cr_bill_type,'') = 'C' then 'Course'
    when isnull(cr.cr_bill_type,'') = 'P' then 'Package' else isnull(cr.cr_bill_type,'') end,
    cbm.cbm_batch_start_dt,
    Paymenttype = case when isnull(cr.cr_reg_type,'') = '' then 'Not Paid'
    when isnull(cr.cr_reg_type,'') = null then 'Not Paid'
    else '' end,
    pendingamt = (select top 1 isnull(b.bill_pend_amt,'') from bill_file b where
    b.cr_bill_no = cr.cr_bill_no and b.bill_active <> 'D')
    from course_registration cr, batch_course_registration bcr, co_batch_master cbm
    where bcr.cr_bill_no = cr.cr_bill_no and cr.cr_active = 'A'
    and bcr.bcr_batch_id = cbm.cbm_batch_id
    and cr.cmj_major_code <> 'PSTF'
    and isnull(cr.cr_reg_type,'') <> 'C'

    The view name as follows
    Views_Fees_Pending


    i want to use above views(Views_Fees_Pedning) inside the stored procedure.

    for that how can i do in stored procedure.

    please help me.

    Regards,
    Narasiman P.
  • #747348
    Create procedure typeHereStoredProcedureName (@typeHereParameterName dataType)
    as
    begin
    select *from typeHereViewName where typeHereColumnNameWhichUsingAsParameter=@typeHereParameterName
    end

  • #747352
    Hi,

    If you want to use View in your procedure there is no separate procedure for that. How you can use table inside procedure same method you just follow while calling View inside procedure.

    EX:

    create procedure procname
    (
    --parameters
    )
    as
    begin
    select * from viewname
    end


    Try something like above to achieve your goal...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #747386
    A view is a virtual table so just use a select statement on the view like you use for a table. The WHERE clause would be the parameter that you pass as filter.


  • Sign In to post your comments