November 10, 2011 at 9:25 am
Hi
I am practicing queries with some of own requirement so query may sounds crazy but please help me.
select * from Sales.SalesOrderDetail
--function creation
create function udf_getavgLineTotal (@ProductID int)
returns int
as
begin
declare @avgvalue int
select @avgvalue=AVG(UnitPrice) from Sales.SalesOrderDetail where ProductID =@ProductID
return @avgvalue
end
--function execution
select dbo.udf_getavgLineTotal (777)
--new table
Create table AvgUnitTable
(ProductID int,UnitPrice int,avgunitprice int)
select * from AvgUnitTable
--new table type parameter
CREATE TYPE AvgUnitTableType as table
(ProductID int,UnitPrice int,avgunitprice int)
--procedure
alter procedure usp_avgunitprice @TableName AvgUnitTableType readonly
as
insert into AvgUnitTable
(ProductID,UnitPrice,avgunitprice)
select ProductID,UnitPrice,dbo.udf_getavgLineTotal(UnitPrice )
from @TableName
--procedure execution
declare @NewValue as AvgUnitTableType
insert into @NewValue
select ProductID,UnitPrice,avgunitprice from AvgUnitTable
exec usp_avgunitprice @NewValue
What I want is :
-->topic is using functions inside procedures
-->created scalar function ,by using table valued parameter I want to insert average of unite price into new table.But dont know how actually it works.Or may be my idea on that table might be wrong.
Can some one help me.There is no error but it returns 0 values.I am using Adventuresworks2008r2 database Thank you
November 10, 2011 at 10:07 am
You want to be really careful with functions. You can cause lots of performance issues with using them.
That being said, it looks like you are passing unitprice and not product ID to your function:
insert into AvgUnitTable
(ProductID,UnitPrice,avgunitprice)
select ProductID,UnitPrice,dbo.udf_getavgLineTotal(UnitPrice )
from @TableName
November 10, 2011 at 11:07 am
Oh my god i cant believe yes iam using unit price.....
i will run it and let you know
thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply