View - SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

View - SP

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-05-20 : 09:05:05

I have this view:

select top 1 Convert(Char(10),po.order_date,101) as OrderDate
,poitem.qty_received
,poitem.item_cost
,poitem.item
,po.po_num
from po
inner join poitem on poitem.po_num = po.po_num
order by po.order_date desc

I want to then create a SP that is going to pass a variable like so:

procedure AM_RptPurchaseHistorySP(
@item nvarchar (12) = null)
as
select orderdate,item_cost,qty_received,po_num
from AM_PurchaseHistory
where item = @item

In the view I am using the select top 1 with order to get the last order_date record. I think this might be messing with my SP, as I am getting not returns. Tested trying:
select * from am_PurchaseHistory where item = '1618719'

Any thoughts?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 10:10:51

If I understand you requirement correctly...this may work..
Use this query to create the view.

select * from 
(
select row_number() over(partition by poitem.item order by po.order_date desc) as seq
,convert(varchar(10),po.order_date ,101) as OrderDate
,poitem.qty_received
,poitem.item_cost
,poitem.item
,po.po_num
from po
inner join poitem on poitem.po_num = po.po_num
) t
where t.seq = 1

And your procedure can contain the same query that you have used..

select orderdate,item_cost,qty_received,po_num 
from AM_PurchaseHistory
where item = @item

Try it and tell us if it worked.

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-05-20 : 10:36:48

That is perfect. Thanks for the lesson!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 11:37:08

no problem..welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources