Solved

SQL - User Defined Function

Posted on 2011-02-16
9
285 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SubQuery link 4 35
Format Data Field - SQL 11 37
SQL Use Distinct with two fields 3 14
TSQL Query Into Specific XML Format w/ Multiple Groupings 6 33
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 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