Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL - User Defined Function

Posted on 2011-02-16
9
Medium Priority
?
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34911438
This can all be done id a stored procedure or a Table Valued function
0
 
LVL 60

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 60

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:Ephraim Wangoya
Ephraim Wangoya earned 1000 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 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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