Solved

Help with sql server stored procedure

Posted on 2013-11-08
11
329 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 48
Testing connection to sql 7 60
SQL Merige returns error code when updating 15 53
Whats wrong in this query - Select * from tableA,tableA 11 32
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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