?
Solved

SQL - User Defined Function

Posted on 2011-02-16
9
Medium Priority
?
289 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

743 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