Solved

SQL - User Defined Function

Posted on 2011-02-16
9
283 Views
Last Modified: 2012-08-14

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

0
Comment
Question by:chokka
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34911438
This can all be done id a stored procedure or a Table Valued function
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 34911450
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.  
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34911461
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

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 34911469
If you need a view, you can look to rewrite this in what looks like a UNION [ALL] scenario.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:chokka
ID: 34911482
tigin44 - Total number of rows affected varies from your query to the my sql script
0
 

Author Comment

by:chokka
ID: 34911489
Can anyone can help me to put all my script in a Table Valued Function ... Which i am not familiar ..!
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 250 total points
ID: 34911494
Here is the exact code in a function
-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION Table_Function_Name()
RETURNS 
	@ResultTable TABLE 
	(
		[Generic Code] nvarchar(1000),
		NDC nvarchar(1000),   
		[Drug Name]	nvarchar(1000),   
		Qty nvarchar(1000)   
	)
AS
BEGIN
	
Insert Into @ResultTable ([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 @ResultTable ([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

  	RETURN 
END
GO

Open in new window

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 250 total points
ID: 34911499
the UNION operator eliminateds duplicates... if you want to get the whole result set you should replace UNION with UNION ALL
0
 

Author Closing Comment

by:chokka
ID: 34911579
Both Tigin44  and ewangoya
are right.

I have misunderstood Tigin44 - Union Keyword
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 4 45
Strange msg in the SSMS pane 13 50
T-SQL Update Table using Input Parameter as Column Name 6 41
Query to return total 6 0
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now