Link to home
Create AccountLog in
Avatar of chokka
chokkaFlag for United States of America

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

Open in new window

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

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

Open in new window

If you need a view, you can look to rewrite this in what looks like a UNION [ALL] scenario.
Avatar of chokka

ASKER

tigin44 - Total number of rows affected varies from your query to the my sql script
Avatar of chokka

ASKER

Can anyone can help me to put all my script in a Table Valued Function ... Which i am not familiar ..!
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of chokka

ASKER

Both Tigin44  and ewangoya
are right.

I have misunderstood Tigin44 - Union Keyword