June 8, 2011 at 1:23 pm
I am trying to find some help writing this stored procedure, I will give you what i got with a short explanation on what i am trying to accomplish.
In .NET i will have a simple web form with parameters the user can select a dater range and two separate lists of things.
One will be products the other will be offices, the offices will have sub offices "we will call them practices". But Offices will have unique names added to our list.
In. Net its the list is converted to an XML structure. My function is to return a stored procedure as a datatable, the sp will have 4 parameters
@BeginDateRange as Date
@EndDateRange as Date
@Offices as XML
@Drugs as XML
I am having issues writing the procedure this is what i have put down so far but i have hit a wall.
If you feel like helping me i would appreciate it.
XML would look like
<Offices>
<OfficeName>
<PracticeName>Blue</PracticeName>
<PracticeName>Red</PracticeName>
<PracticeName>Maple</PracticeName>
</OfficeName>
<OfficeName>
<PracticeName>Bluegrass</PracticeName>
<PracticeName>Blah</PracticeName>
<PracticeName>Rye</PracticeName>
</OfficeName>
</Offices>
USE [OpiAmber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sproc_Report_SalesByOffice (
@BeginDateRange as Date,
@EndDateRange as Date,
@Offices as XML,
@Drugs as XML)
AS
Declare @OfficesTable Table(OfficeName as VARCHAR(20),PracticeName as VARCHAR(20))
Insert Into @OfficesTable (OfficeName, PracticeName) SELECT ParamValues.OfficeName.value('.','VARCHAR(MAX)'), ParamValues.PracticeName.value('.','VARCHAR(MAX)')
FROM @Offices.nodes('/Offices/OfficeName', '/Offices/OfficeName/PracticeName') as ParamValues(OfficeName, PracticeName)
Declare @DrugTable Table (Products as Varchar(MAX))
Insert Into @DrugTable (Products) SELECT ParamValues.Products.value('.','VARCHAR(MAX)')
From @Drugs.nodes('/Drugs/Products') as ParamValues(Products)
BEGIN
SELECT Practices.PracticeName, Offices.OfficeName, Users.UserLastName, Users.UserFirstName, Orders.OrderDeliveryTimestamp, Patients.PatientFirstName,
Patients.PatientLastName, Products.ProductName, Orders.OrderID, OrderItems.OrderItemTotalPrice, OrderItems.OrderItemDose
FROM OrderItems INNER JOIN
Orders ON OrderItems.OrderID = Orders.OrderID INNER JOIN
Patients ON Orders.PatientID = Patients.PatientID INNER JOIN
Offices ON Patients.OfficeID = Offices.OfficeID INNER JOIN
Practices ON Orders.PracticeID = Practices.PracticeID AND Patients.PracticeID = Practices.PracticeID AND Offices.PracticeID = Practices.PracticeID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID INNER JOIN
Users ON Orders.UserID_Activated = Users.UserID AND Orders.UserID_Placed = Users.UserID AND Orders.UserID_BuildPharmacist = Users.UserID AND
Orders.UserID_Administered = Users.UserID AND Orders.UserID_Signed = Users.UserID AND Orders.UserID_Administered = Users.UserID AND
Orders.UserID_ReleasePharmacist = Users.UserID AND Orders.UserID_Placed = Users.UserID AND Orders.UserID_PeerPharmacist = Users.UserID AND
Patients.UserID = Users.UserID
Where Orders.OrderDeliveryTimestamp between @BeginDateRange And @EndDateRange
END
'
Ok lets forget the temp tables how about joining the xml being passed in with the database
i know a statement like this would work, but how would i join to the database
Declare @Drugs xml, @Offices xml, @BeginDateRange as Date, @EndDateRange as Date
SET @BeginDateRange = '01/04/2012'
SET @EndDateRange = '01/03/2012'
SET @Offices ='<Offices><Practice ID="6" /><Practice ID="7" /><Practice ID="8" /></Offices>'
SET @Drugs ='<Drugs><Product ID="6" /><Product ID="7" /><Product ID="8" /></Drugs>'
SELECT Products.ProductName, Products.ProductID
from Products
WHERE Products.ProductID IN
(SELECT T.c.value('@ID', 'integer') As result
FROM @Drugs.nodes('/Drugs/Product') as T(c))
June 8, 2011 at 2:26 pm
So where is the problem? There is a lot of unfinished code in here. Are your table variables being filled correctly? You are not using those table variables in the select. You have set the date range beginning and ending to the same value. It looks like your orders table has a datetime datatype and you are declaring start and end as dates. This will exclude any orders placed on the last day of your range. Why in the world do you require so many fields in the Orders table to have the same UserID???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2011 at 3:46 pm
Sean i guess i have many problems, the main one is i hit a wall. First can i join the xml being passed in the SP to the database ? Skipping the temp tables ?
I just want to pass in 2 dates pull back the range.
Pass in OfficeXML with a list of office and practice ID's pull back the matching ID's from the tables.
And pass in ProductsXML and pull back the products with matching IDs.
I know how to do this all separately.
SELECT Orders.OrderDeliveryTimestamp
from orders
where orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange
or
Declare @Drugs xml, @Offices xml, @BeginDateRange as Date, @EndDateRange as Date
SET @BeginDateRange = '01/04/2012'
SET @EndDateRange = '01/03/2012'
SET @Offices ='<Offices><Practice ID="6" /><Practice ID="7" /><Practice ID="8" /></Offices>'
SET @Drugs ='<Drugs><Product ID="6" /><Product ID="7" /><Product ID="8" /></Drugs>'
SELECT Products.ProductName, Products.ProductID
from Products
WHERE Products.ProductID IN
(SELECT T.c.value('@ID', 'integer') As result
FROM @Drugs.nodes('/Drugs/Product') as T(c))
I just cant for the life of me put it all together.
As far as the userID's i haven't the slightest clue why they are like that.
I just need to pull back all those fields for the report, and the query designer joined like that.
June 9, 2011 at 8:24 am
Maybe if i ask a more specific question, i can get a better response.
How do i write this where statement to make use of both select statements ?
Declare @Drugs xml, @Offices xml, @BeginDateRange as Date, @EndDateRange as Date
SET @BeginDateRange = '2011/05/12'
SET @EndDateRange = '2011/05/15'
SET @Offices ='<Offices><Practice ID="6" /><Practice ID="7" /><Practice ID="8" /></Offices>'
SET @Drugs ='<Drugs><Product ID="6" /><Product ID="7" /><Product ID="8" /></Drugs>'
SELECT Products.ProductName, Products.ProductID, Offices.OfficeName, Practices.PracticeName
FROM Orders INNER JOIN
Practices ON Orders.PracticeID = Practices.PracticeID INNER JOIN
Offices ON Practices.PracticeID = Offices.PracticeID CROSS JOIN
Products
WHERE Products.ProductID and practices.PracticeID in
(SELECT T.c.value('@ID', 'integer') As result
FROM @Drugs.nodes('/Drugs/Product') as T(c)) and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange)
and (SELECT T.c.value('@ID', 'integer') As result
FROM @Offices.nodes('/Offices/Practice') as T(c)
June 9, 2011 at 8:27 am
This was removed by the editor as SPAM
June 15, 2011 at 2:41 pm
See below for a final example of how i accomplish my tasks
June 15, 2011 at 2:51 pm
Glad you found a solution and thanks for posting your final code. Will definitely help others having similar issues down the road.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 15, 2011 at 3:16 pm
For future reference for people who come across this down the line the following sproc on the Database side handled everything i need it to handle
In .NET i have a simple web application with parameters the user can select. They are a date range and two separate data repeaters that contain values from two tables within a database, Offices and Drugs. The user can select all dates, a range of dates or a specific date.
The data repeaters contain check boxes and the user selects what offices and drugs they wish to return a report (query) about.
This is specific to orders that have been place at these offices for these drugs.
Steps to the goal.
In .NET this code puts the user selected information to a list.
Private Function SelectedOffices() As List(Of Integer)
Dim OfficeList As New List(Of Integer)
For Each OfficeItem As RepeaterItem In Me.OfficeParentRepeater.Items
Dim OfficeCheckBox As CheckBox = DirectCast(OfficeItem.FindControl("OfficeCheckBox"), CheckBox)
If OfficeCheckBox IsNot Nothing Then
If OfficeCheckBox.Checked Then
Dim HiddenOfficeID As HiddenField = DirectCast(OfficeItem.FindControl("OfficeIDHiddenField"), HiddenField)
Dim ID As Integer = CInt(HiddenOfficeID.Value.ToString)
OfficeList.Add(ID)
End If
Else
RadioButtonListOffices.SelectedValue = "SelectAllOffices"
CheckOffices()
End If
Next
Return OfficeList
End Function
Then we convert that to XML using Linq.
Private Function OfficeIDsToXML() As XDocument
Dim OfficeDoc As XDocument = <?xml version='1.0'?>
<Root>
<%= RenderKeys(SelectedOffices) %>
</Root>
Return OfficeDoc
End Function
Private Function RenderKeys(ByVal keys As List(Of Integer)) As Collection(Of XElement)
Dim ElementCollection As New Collection(Of XElement)
For Each Key As Integer In keys
Dim XKey As XElement = <Key ID=<%= Key %>/>
ElementCollection.Add(XKey)
Next
Return ElementCollection
End Function
Now we have xml data 🙂 yeah !!!
The next function creates our connection passes in our sproc and parameters and returns a data table.
Private Shared Function ExecuteDataTable(ByVal sprocName As String, ByVal parameters As Collections.ObjectModel.Collection(Of SqlParameter)) As DataTable
Dim Connection As New SqlConnection(AmberData.Settings.ConnectionStrings.OpiAmber)
Dim Command As New SqlCommand
Dim DataAdapter As New SqlDataAdapter(Command)
Dim ResultTable As New DataTable
Command.Connection = Connection
Command.CommandType = CommandType.StoredProcedure
Command.CommandText = sprocName
For Each Param As SqlParameter In parameters
Command.Parameters.Add(Param)
Next
Connection.Open()
DataAdapter.Fill(ResultTable)
Command.Dispose()
Connection.Dispose()
Return ResultTable
End Function
Our 2 Functions to pass in our parameters and execute our previous function.
Public Shared Function sproc_Report_SalesByOfffice(ByVal BeginDate As Date, ByVal EndDate As Date, ByVal DrugXml As XDocument, ByVal OfficeXml As XDocument) As DataTable
Dim SprocName As String = "Sproc_Report_SalesByOffice"
Dim Parameters As New Collection(Of SqlParameter)
'Dim ReadDrugXml As New SqlXml(File.OpenRead(DrugXml))
'Dim ReadOfficeXml As New SqlXml(File.OpenRead(OfficeXml))
AddParameter("@BeginDateRange", BeginDate, SqlDbType.Date, Parameters)
AddParameter("@EndDateRange", EndDate, SqlDbType.Date, Parameters)
AddParameter("@Drugs", DrugXml.ToString, SqlDbType.Xml, Parameters)
AddParameter("@Offices", OfficeXml.ToString, SqlDbType.Xml, Parameters)
Return ExecuteDataTable(SprocName, Parameters)
End Function
Private Shared Sub AddParameter(ByVal paramName As String, ByVal value As Object, ByVal dbType As SqlDbType, ByVal params As Collection(Of SqlParameter))
Dim Parameter As New SqlParameter(paramName, dbType)
Parameter.Value = value
params.Add(Parameter)
End Sub
Our stored Procedure that takes out parameters and returns our dynamically created query.
ALTER PROCEDURE [dbo].[Sproc_Report_SalesByOffice] (
@BeginDateRange as Date,
@EndDateRange as Date,
@Offices as XML,
@Drugs as XML)
AS
BEGIN
SELECT Practices.PracticeName, Offices.OfficeName, Users.UserLastName, Users.UserFirstName, Orders.OrderDeliveryTimestamp, Patients.PatientFirstName,
Patients.PatientLastName, Products.ProductName, Orders.OrderID, OrderItems.OrderItemTotalPrice, OrderItems.OrderItemDose
FROM OrderItems INNER JOIN
Orders ON OrderItems.OrderID = Orders.OrderID INNER JOIN
Patients ON Orders.PatientID = Patients.PatientID INNER JOIN
Offices ON Patients.OfficeID = Offices.OfficeID INNER JOIN
Practices ON Orders.PracticeID = Practices.PracticeID AND Patients.PracticeID = Practices.PracticeID AND Offices.PracticeID = Practices.PracticeID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID INNER JOIN
Users ON Orders.UserID_Activated = Users.UserID AND Orders.UserID_Placed = Users.UserID AND Orders.UserID_BuildPharmacist = Users.UserID AND
Orders.UserID_Administered = Users.UserID AND Orders.UserID_Signed = Users.UserID AND Orders.UserID_Administered = Users.UserID AND
Orders.UserID_ReleasePharmacist = Users.UserID AND Orders.UserID_Placed = Users.UserID AND Orders.UserID_PeerPharmacist = Users.UserID AND
Patients.UserID = Users.UserID
WHERE Products.ProductID in
(SELECT T.c.value('@ID', 'integer') As result FROM @Drugs.nodes('/Root') as T(c))
and Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root') as T(c) )
and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange)
End
Now you have a datatable that is created on the fly to poulate your .rdlc or anything else you wish to do with it. Since i no longer am using any SQL you can just research on what to do from there.
Thank you to all who helped
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply