?
Solved

Help with sql server stored procedure

Posted on 2013-11-08
11
Medium Priority
?
336 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

771 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