chokka
asked on
SQL - User Defined Function
Is there any way for me to keep all these code inside a User Defined Function.
I am sure that , i can't keep these codes inside a VIEW.
declare @TempTable table
(
[Generic Code] nvarchar(1000),
NDC nvarchar(1000),
[Drug Name] nvarchar(1000),
Qty nvarchar(1000)
)
Insert Into @TempTable ([Generic Code],NDC,[Drug Name],Qty)
select
TR.[Generic Code],
TR.NDC,
TR.[Drug Name],
Sum(TR.[Dispensed Qty]) as Qty
from TransactionReport TR
Group By TR.[Generic Code],TR.NDC,TR.[Drug Name]
Insert Into @TempTable ([Generic Code],NDC,[Drug Name],Qty)
select Dr.GenericCode as [Generic Code],Dr.Name as [Drug Name],Br.NDC as [NDC],Br.Qty as [Qty] from BalanceReport Br
Inner Join Drug Dr
on
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') = Br.NDC
select [Generic Code],NDC,[Drug Name],Qty from @TempTable
This can all be done id a stored procedure or a Table Valued function
Without looking more carefully, I won't comment as to should you; however, just answering your question: yes, you can do that in a table valued function.
you can use this select as a view
select
TR.[Generic Code],
TR.NDC,
TR.[Drug Name],
Sum(TR.[Dispensed Qty]) as Qty
from TransactionReport TR
Group By TR.[Generic Code],TR.NDC,TR.[Drug Name]
UNION
select Dr.GenericCode as [Generic Code],Dr.Name as [Drug Name],Br.NDC as [NDC],Br.Qty as [Qty]
from BalanceReport Br
Inner Join Drug Dr ON stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1
when Dr.DrugNDCType in (51, 57) then 6
when Dr.DrugNDCType = 52 then 10 end, 0, '0') = Br.NDC
If you need a view, you can look to rewrite this in what looks like a UNION [ALL] scenario.
ASKER
tigin44 - Total number of rows affected varies from your query to the my sql script
ASKER
Can anyone can help me to put all my script in a Table Valued Function ... Which i am not familiar ..!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both Tigin44 and ewangoya
are right.
I have misunderstood Tigin44 - Union Keyword
are right.
I have misunderstood Tigin44 - Union Keyword