Solved

Help with sql server stored procedure

Posted on 2013-11-08
11
332 Views
Last Modified: 2013-11-10
Hey guys!!
I am trying to create a stored procedure using 2 Select statements.  I am passing one variable from the C# program (@RxNum) and I need to get 2 variables from the first statement to use in the last 2nd select statement.  (@NDC9 and @LangPref) values should come from the first select statement.

USE [BarCodeSQL]
GO
/****** Object:  StoredProcedure [dbo].[sp_RxLabel]    Script Date: 11/07/2013 06:45:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_RxLabel]

@RxNum as integer = 0,
@NDC9 AS Text = tblRXNew.NDC9,
@LangPref Text = tblCustomers.LANGUAGEPREF,

AS
BEGIN

	SET NOCOUNT ON;

SELECT     tblCustomers.CKEY, tblCustomers.ADDRESS, tblCustomers.CITY, tblCustomers.STATE, tblCustomers.ZIP, tblCustomers.DOB, tblCustomers.LANGUAGEPREF, tblSig.Sig, tblCustomers.Fullname, tblRXNEW.RecordNum, tblRXNEW.RXNUM, tblRXNEW.REFILL, tblRXNEW.CUSTKEY, tblRXNEW.MEDDEA, tblRXNEW.RETAILPRICE, 
                      tblRXNEW.PAYCODE, tblRXNEW.THDID, tblRXNEW.COPAY, tblRXNEW.COSTPD, tblRXNEW.FEEPAID, tblRXNEW.INSPAID, tblRXNEW.CLAIMREFNUM, 
                      tblRXNEW.DRUG, tblRXNEW.DRUGKEY, tblRXNEW.DOCNAME, tblRXNEW.DOCKEY, tblRXNEW.TRANSDATE, tblRXNEW.PHARMINIT, tblRXNEW.FACEQTY, 
                      tblRXNEW.NH, tblRXNEW.TXTIME, tblRXNEW.Alpha, tblRXNEW.NDC, tblRXNEW.RungUp, tblRXNEW.HCPCS, tblRXNEW.NHCODE, tblRXNEW.DRUGCOST, 
                      tblRXNEW.INSGROUP, tblRXNEW.BIN, tblRXNEW.CLMMSG1, tblRXNEW.CLMMSG2, tblRXNEW.CHGYN, tblRXNEW.RESENDFLAG, tblRXNEW.EZOFF, 
                      tblRXNEW.CANDEL, tblRXNEW.MULTIINS, tblRXNEW.PRISEC, tblRXNEW.FILLDATE, tblRXNEW.RADate, tblRXNEW.RAPaid, tblRXNEW.RAMsg, tblRXNEW.BILLDATE, 
                      tblRXNEW.WORKFLOW, tblRXNEW.PRINTED, tblRXNEW.TECHINIT, tblRXNEW.DATEWRITTEN, tblRXNEW.REFILLUNTIL, tblRXNEW.REFILLSALLOWED, 
                      tblRXNEW.DAYSSUPPLY, tblRXNEW.MAXQUANTITY, tblRXNEW.ORIGINCODE, tblRXNEW.DISPENSEDQTY, tblRXNEW.DAW, tblRXNEW.DUR, tblRXNEW.DrugLot, 
                      tblRXNEW.ExpirationDate, tblRXNEW.EligibilityOverride, tblRXNEW.SCC, tblRXNEW.WhoCalled, tblRXNEW.ssmessageid, tblRXNEW.ssfilename, 
                      tblRXNEW.compound,tblRXNEW.NDC9
FROM         tblCustomers INNER JOIN
                      tblRXNEW ON tblCustomers.CKEY = tblRXNEW.CUSTKEY INNER JOIN
                      tblSig ON tblRXNEW.RXNUM = tblSig.RxNum
WHERE     (tblRXNEW.RXNUM = @RXNUM)

Union

SELECT        Product_Patient_Education.PatientEducationID, Patient_Education.ID, Patient_Education.Language, Patient_Education.SHEET_NAME, Patient_Education.DESCR, 
                         Patient_Education.CONTRA, Patient_Education.ADMIN, Patient_Education.MISSED, Patient_Education.INTER, Patient_Education.MONITOR, Patient_Education.SIDE, 
                         Patient_Education.STORE, Patient_Education.DESCR_HEADERID, Patient_Education.DESCR_FOOTERID, Patient_Education.CONTRA_HEADERID, 
                         Patient_Education.CONTRA_FOOTERID, Patient_Education.ADMIN_HEADERID, Patient_Education.ADMIN_FOOTERID, Patient_Education.MISSED_HEADERID, 
                         Patient_Education.MISSED_FOOTERID, Patient_Education.INTER_HEADERID, Patient_Education.INTER_FOOTERID, Patient_Education.SIDE_HEADERID, 
                         Patient_Education.SIDE_FOOTERID, Patient_Education.MONITOR_HEADERID, Patient_Education.MONITOR_FOOTERID, Patient_Education.STORE_HEADERID, 
                         Patient_Education.STORE_FOOTERID, Patient_Education.LastUpdated
FROM            Product INNER JOIN
                         Product_Patient_Education ON Product.ProductID = Product_Patient_Education.ProductID RIGHT OUTER JOIN
                         Patient_Education ON Product_Patient_Education.PatientEducationID = Patient_Education.ID
WHERE         (Product.NDC9 = @NDC9) AND (Patient_Education.Language = @LangPref)

END

Open in new window

This is supposed to supply the information for a report.  Any help would be appreciated!!!

Jerry
0
Comment
Question by:JDL129
11 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39635322
try this.
USE [BarCodeSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_RxLabel]

@RxNum  int = 0

AS
BEGIN
declare @NDC9 nvarchar(100), @LangPref nvarchar(100)


      SET NOCOUNT ON;

SELECT     
@LangPref = tblCustomers.LANGUAGEPREF 
,@NDC9 = tblRXNEW.NDC9
FROM         tblCustomers INNER JOIN
                      tblRXNEW ON tblCustomers.CKEY = tblRXNEW.CUSTKEY INNER JOIN
                      tblSig ON tblRXNEW.RXNUM = tblSig.RxNum
WHERE     (tblRXNEW.RXNUM = @RXNUM)

SELECT        Product_Patient_Education.PatientEducationID, Patient_Education.ID, Patient_Education.Language, Patient_Education.SHEET_NAME, Patient_Education.DESCR, 
                         Patient_Education.CONTRA, Patient_Education.ADMIN, Patient_Education.MISSED, Patient_Education.INTER, Patient_Education.MONITOR, Patient_Education.SIDE, 
                         Patient_Education.STORE, Patient_Education.DESCR_HEADERID, Patient_Education.DESCR_FOOTERID, Patient_Education.CONTRA_HEADERID, 
                         Patient_Education.CONTRA_FOOTERID, Patient_Education.ADMIN_HEADERID, Patient_Education.ADMIN_FOOTERID, Patient_Education.MISSED_HEADERID, 
                         Patient_Education.MISSED_FOOTERID, Patient_Education.INTER_HEADERID, Patient_Education.INTER_FOOTERID, Patient_Education.SIDE_HEADERID, 
                         Patient_Education.SIDE_FOOTERID, Patient_Education.MONITOR_HEADERID, Patient_Education.MONITOR_FOOTERID, Patient_Education.STORE_HEADERID, 
                         Patient_Education.STORE_FOOTERID, Patient_Education.LastUpdated
FROM            Product INNER JOIN
                         Product_Patient_Education ON Product.ProductID = Product_Patient_Education.ProductID RIGHT OUTER JOIN
                         Patient_Education ON Product_Patient_Education.PatientEducationID = Patient_Education.ID
WHERE         (Product.NDC9 = @NDC9) AND (Patient_Education.Language = @LangPref)

END

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 39636582
Hi,

A PortletPaul mentioned, please watch the formatting. Better formatting is easier for humans to read!

I need to explain what I've done to arrive at the below code.

Each clause fragment is on its own line

Indenting is consistent

All tables are aliased, and those aliases used instead of the table names.

All tables are qualified with the schema name, using the default schema of dbo. This is SQL and not one of the other database topic areas.

I've merged the two queries into one. There possibly an extra join that isn't required in the first query.

I've changed the right outer join to an inner join. Reason: Right outer joins are hard to code correctly and most of the time a left outer join is more correct, and secondly, the where clause suggests this actually an inner join.

HTH
  David

SELECT        
	PPE.PatientEducationID
	, PE.ID
	, PE.Language
	, PE.SHEET_NAME
	, PE.DESCR
	, PE.CONTRA
	, PE.ADMIN
	, PE.MISSED
	, PE.INTER
	, PE.MONITOR
	, PE.SIDE
	, PE.STORE
	, PE.DESCR_HEADERID
	, PE.DESCR_FOOTERID
	, PE.CONTRA_HEADERID
	, PE.CONTRA_FOOTERID
	, PE.ADMIN_HEADERID
	, PE.ADMIN_FOOTERID
	, PE.MISSED_HEADERID
	, PE.MISSED_FOOTERID
	, PE.INTER_HEADERID
	, PE.INTER_FOOTERID
	, PE.SIDE_HEADERID
	, PE.SIDE_FOOTERID
	, PE.MONITOR_HEADERID
	, PE.MONITOR_FOOTERID
	, PE.STORE_HEADERID
	, PE.STORE_FOOTERID
	, PE.LastUpdated
-- first query
FROM dbo.tblCustomers c
INNER JOIN dbo.tblRXNEW n 
	ON c.CKEY = n.CUSTKEY 
-- I cant see any use for this join
--INNER JOIN dbo.tblSig s
--	ON n.RXNUM = s.RxNum

--joinned to second query
inner join dbo.Product p 
	on p.NDC9 = n.NDC9
-- RIGHT OUTER JOIN 
inner dbo.Patient_Education pe
	ON PPE.PatientEducationID = PE.ID
	and PE.Language = c.LANGUAGEPREF

INNER JOIN dbo.Product_Patient_Education ppe 
	ON p.ProductID = PPE.ProductID 
-- where from first query
WHERE     
	n.RXNUM = @RXNUM
;

Open in new window

0
 

Author Comment

by:JDL129
ID: 39636936
Thanks guys for the post!!!!

I also need the values from the first query.

THANKS AGAIN!!!

Jerry
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:JDL129
ID: 39637071
Thanks so much for helping!!!!!!!!!!!!!
I modified the code so I get the values from the first query and the second query but they are separate and only the first query show up on my label.  How would I go about combining the two so that both would show up as fields for my label?


USE [BarCodeSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_RxLabel]

@RxNum  int = 0

AS
BEGIN
declare @NDC9 nvarchar(100), @LangPref nvarchar(100)


      SET NOCOUNT ON;

SELECT     
@LangPref = tblCustomers.LANGUAGEPREF 
,@NDC9 = tblRXNEW.NDC9
FROM         tblCustomers INNER JOIN
                      tblRXNEW ON tblCustomers.CKEY = tblRXNEW.CUSTKEY INNER JOIN
                      tblSig ON tblRXNEW.RXNUM = tblSig.RxNum
WHERE     (tblRXNEW.RXNUM = @RXNUM)

SELECT     tblCustomers.CKEY, tblCustomers.ADDRESS, tblCustomers.CITY, tblCustomers.STATE, tblCustomers.ZIP, tblCustomers.DOB, tblSig.Sig, tblCustomers.Fullname, tblRXNEW.COPAY AS Expr5, tblRXNEW.RecordNum, tblRXNEW.RXNUM, tblRXNEW.REFILL, tblRXNEW.CUSTKEY, tblRXNEW.MEDDEA, tblRXNEW.RETAILPRICE, 
                      tblRXNEW.PAYCODE, tblRXNEW.THDID, tblRXNEW.COPAY, tblRXNEW.COSTPD, tblRXNEW.FEEPAID, tblRXNEW.INSPAID, tblRXNEW.CLAIMREFNUM, 
                      tblRXNEW.DRUG, tblRXNEW.DRUGKEY, tblRXNEW.DOCNAME, tblRXNEW.DOCKEY, tblRXNEW.TRANSDATE, tblRXNEW.PHARMINIT, tblRXNEW.FACEQTY, 
                      tblRXNEW.NH, tblRXNEW.TXTIME, tblRXNEW.Alpha, tblRXNEW.NDC, tblRXNEW.RungUp, tblRXNEW.HCPCS, tblRXNEW.NHCODE, tblRXNEW.DRUGCOST, 
                      tblRXNEW.INSGROUP, tblRXNEW.BIN, tblRXNEW.CLMMSG1, tblRXNEW.CLMMSG2, tblRXNEW.CHGYN, tblRXNEW.RESENDFLAG, tblRXNEW.EZOFF, 
                      tblRXNEW.CANDEL, tblRXNEW.MULTIINS, tblRXNEW.PRISEC, tblRXNEW.FILLDATE, tblRXNEW.RADate, tblRXNEW.RAPaid, tblRXNEW.RAMsg, tblRXNEW.BILLDATE, 
                      tblRXNEW.WORKFLOW, tblRXNEW.PRINTED, tblRXNEW.TECHINIT, tblRXNEW.DATEWRITTEN, tblRXNEW.REFILLUNTIL, tblRXNEW.REFILLSALLOWED, 
                      tblRXNEW.DAYSSUPPLY, tblRXNEW.MAXQUANTITY, tblRXNEW.ORIGINCODE, tblRXNEW.DISPENSEDQTY, tblRXNEW.DAW, tblRXNEW.DUR, tblRXNEW.DrugLot, 
                      tblRXNEW.ExpirationDate, tblRXNEW.EligibilityOverride, tblRXNEW.SCC, tblRXNEW.WhoCalled, tblRXNEW.ssmessageid, tblRXNEW.ssfilename, 
                      tblRXNEW.compound
FROM         tblCustomers INNER JOIN
                      tblRXNEW ON tblCustomers.CKEY = tblRXNEW.CUSTKEY INNER JOIN
                      tblSig ON tblRXNEW.RXNUM = tblSig.RxNum
WHERE     (tblRXNEW.RXNUM = @RXNUM)

SELECT        Product_Patient_Education.PatientEducationID, Patient_Education.ID, Patient_Education.Language, Patient_Education.SHEET_NAME, Patient_Education.DESCR, 
                         Patient_Education.CONTRA, Patient_Education.ADMIN, Patient_Education.MISSED, Patient_Education.INTER, Patient_Education.MONITOR, Patient_Education.SIDE, 
                         Patient_Education.STORE, Patient_Education.DESCR_HEADERID, Patient_Education.DESCR_FOOTERID, Patient_Education.CONTRA_HEADERID, 
                         Patient_Education.CONTRA_FOOTERID, Patient_Education.ADMIN_HEADERID, Patient_Education.ADMIN_FOOTERID, Patient_Education.MISSED_HEADERID, 
                         Patient_Education.MISSED_FOOTERID, Patient_Education.INTER_HEADERID, Patient_Education.INTER_FOOTERID, Patient_Education.SIDE_HEADERID, 
                         Patient_Education.SIDE_FOOTERID, Patient_Education.MONITOR_HEADERID, Patient_Education.MONITOR_FOOTERID, Patient_Education.STORE_HEADERID, 
                         Patient_Education.STORE_FOOTERID, Patient_Education.LastUpdated
FROM            Product INNER JOIN
                         Product_Patient_Education ON Product.ProductID = Product_Patient_Education.ProductID RIGHT OUTER JOIN
                         Patient_Education ON Product_Patient_Education.PatientEducationID = Patient_Education.ID
WHERE         (Product.NDC9 = @NDC9) AND (Patient_Education.Language = @LangPref)

END

Open in new window


THANKS AGAIN FOR YOU HELP!!!!!!!

Jerry
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39637235
Hi,

Try this in your procedure.

Regards
  David

SELECT        
	PPE.PatientEducationID
	, PE.ID
	, PE.Language
	, PE.SHEET_NAME
	, PE.DESCR
	, PE.CONTRA
	, PE.ADMIN
	, PE.MISSED
	, PE.INTER
	, PE.MONITOR
	, PE.SIDE
	, PE.STORE
	, PE.DESCR_HEADERID
	, PE.DESCR_FOOTERID
	, PE.CONTRA_HEADERID
	, PE.CONTRA_FOOTERID
	, PE.ADMIN_HEADERID
	, PE.ADMIN_FOOTERID
	, PE.MISSED_HEADERID
	, PE.MISSED_FOOTERID
	, PE.INTER_HEADERID
	, PE.INTER_FOOTERID
	, PE.SIDE_HEADERID
	, PE.SIDE_FOOTERID
	, PE.MONITOR_HEADERID
	, PE.MONITOR_FOOTERID
	, PE.STORE_HEADERID
	, PE.STORE_FOOTERID
	, PE.LastUpdated
	, c.LANGUAGEPREF 
	, n.NDC9
-- first query
FROM dbo.tblCustomers c
INNER JOIN dbo.tblRXNEW n 
	ON c.CKEY = n.CUSTKEY 
-- I cant see any use for this join
--INNER JOIN dbo.tblSig s
--	ON n.RXNUM = s.RxNum

--joinned to second query
inner join dbo.Product p 
	on p.NDC9 = n.NDC9
-- RIGHT OUTER JOIN 
inner dbo.Patient_Education pe
	ON PPE.PatientEducationID = PE.ID
	and PE.Language = c.LANGUAGEPREF

INNER JOIN dbo.Product_Patient_Education ppe 
	ON p.ProductID = PPE.ProductID 
-- where from first query
WHERE     
	n.RXNUM = @RXNUM
;

Open in new window

0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39637518
Just a note for those unaware, to get your code into a code block and separate from the normal conversational text, use the word "code" in square brackets ("[" and "]") at the beginning of the code and the word "/code" in square brackets ("[" and "]") at the end of the code (think using HTML but with square brackets instead of angled carets).



Mike
0
 

Author Comment

by:JDL129
ID: 39637684
Hey guys!!  I was unaware of that procedure but if you will notice in my last post the code was in it's own space.  Please don't beat me over the head!

David, I placed your code in a stored procedure and it wouldn't compile.  I have a lot of trouble trying to read code when aliases have been used.

Sorry,

Jerry
0
 

Author Closing Comment

by:JDL129
ID: 39637757
Thanks!!!

It got me in the right directions!!!

Jerry
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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