January 26, 2012 at 4:58 pm
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
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
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
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
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
January 27, 2012 at 3:37 pm
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply