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

x
?
Solved

Help with sql server stored procedure

Posted on 2013-11-08
11
Medium Priority
?
337 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
[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
11 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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