Post reply

View and Stored Procedures

  • January 26, 2012 at 4:58 pm

    #249740

    First I want to say thanks for those that contribute to this site and the service you provide. Second that I am new to SQL Server so I hope this is where my Post belongs. Lastly I did read the posting etiquette but I don't have any sample code or data to provide because I'm not sure if this is possible in SQL. I am trying to create a view that would also call a stored procedure which would populate a user defined table.

    I have been told not to use #temp or ##temp or tempdb tables.

    What I need to accomplish is to create a view that can select a list of fields and a list of calculated values returned from a stored procedure which is passed a pair of parameters, and done in a single pass. I know it is possible to query the data for each calculated value but i would like to use a stored procedure to calculate the list of values and return it.

    This is what I am trying to do.

    SELECT item, warehouse, OnSO(@item, @warehouse) AS openOrders,

    OnPO(@item, @warehouse) AS openPurchases

    FROM myinventorytable

    or

    SELECT item,warehouse,mystoredprocedure.openorders,

    mystoredprocedure.openpurchases

    FROM myinventorytable

    INNER JOIN mystoredprocedure ON

    myinventorytable.item = mystoredprocedure.item

    AND myinventorytable = mystoredprocedure.warehouse

    Again I apologize for not providing sample code. I am having a difficult time with this issue and can't seem to find a solution.

  • January 26, 2012 at 5:21 pm

    #1439097

    A view can select only from Tables or other views.

    A better solution would be to create a stored proc instead of a view that can handle the parameters you send in and join to the original table referenced.

    -Roy

  • January 26, 2012 at 11:03 pm

    #1439137

    I believe you're looking for a "Table Valued Function"

    Select

    i.item,

    i.warehouse,

    f.openOrders,

    f.openPurchases

    from myinventorytable i

    cross apply myTableValuedFunction(i.item, i.warehouse) f

  • January 27, 2012 at 2:53 pm

    #1439627

    Thanks for your reply and your help

    I believe your right. I have created a function that returns a table and I can join the table to the function. If I call the function from a new query not a view and set scalar valued parameters it works. But this ....

    SELECT

    dbo.inwaredt.itemnum,

    dbo.inwaredt.whnum,

    dbo.inwaredt.binloc,

    dbo.inwaredt.reordmeth,

    dbo.inwaredt.ecoqty,

    dbo.inwaredt.reoqty,

    dbo.inwaredt.minqty,

    dbo.inwaredt.maxonhand,

    dbo.inwaredt.begavgcost,

    dbo.inwaredt.begbalqty,

    dbo.inwaredt.qtyrecvd,

    dbo.inwaredt.qtyadjust,

    dbo.inwaredt.qtysold,

    dbo.inwaredt.qtyonhand,

    dbo.inwaredt.qtyonpord,

    dbo.inwaredt.qtyonsord,

    dbo.inwaredt.qtyonbord,

    dbo.inwaremm.whdesc,

    ItemWarehouse.OpenSales AS exp_19,

    ItemWarehouse.OpenPurchases AS exp_20,

    ItemWarehouse.qtyavailable AS exp_21,

    ItemWarehouse.instock,

    ItemWarehouse.transin ,

    ItemWarehouse.transout,

    ItemWarehouse.due,

    ItemWarehouse.comm,

    ItemWarehouse.onwo,

    dbo.inwaredt.seasonal,

    dbo.inwaredt.safetystock,

    dbo.inwaredt.pribinqty,

    dbo.inwaredt.alt1binqty,

    dbo.inwaredt.alt2binqty,

    dbo.inwaredt.altbinloc,

    dbo.inwaredt.altbinloc2,

    dbo.inwaredt.itemcat

    FROM dbo.inwaredt

    LEFT OUTER JOIN

    dbo.inwaremm ON inwaredt.whnum = dbo.inwaremm.whnum

    LEFT OUTER JOIN dbo.GetItemWarehouse(dbo.inwaredt.itemnum, dbo.inwaredt.whnum) ItemWarehouse

    ON inwaredt.itemnum = ItemWarehouse.itemnum

    AND inwaredt.whnum = ItemWarehouse.whnum

    gets this....

    Msg 4104, Level 16, State 1, Line 41

    The multi-part identifier "dbo.inwaredt.itemnum" could not be bound.

    Msg 4104, Level 16, State 1, Line 41

    The multi-part identifier "dbo.inwaredt.whnum" could not be bound.

  • January 27, 2012 at 3:28 pm

    #1439635

    OUTER APPLY the function, not left outer join.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2012 at 3:37 pm

    #1439637

    My mistake I missed the cross apply I should have tried that first. I tired it and that worked! Thank you !

  • January 27, 2012 at 3:40 pm

    #1439639

    Careful, CROSS APPLY === INNER, if you want the equivalent of an outer join, you need OUTER APPLY.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply