Solved

Help with sql server stored procedure

Posted on 2013-11-08
11
325 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 40

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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore 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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now