locked
need help with query RRS feed

  • Question

  •  

    I need some help creating an efficient storedprocedure for my SqlServer2005 database, here is a simplified version of the situation.

     

    I need for the client to be able to pass in a short list of hostnames and a date associated with each, for example:

    hostA, hostADate

    hostB, hostBDate etc.

     

    I have a datatable ('MyTable') that has the following columns.

     

    refid - primary key -uniqueidentifier

    createdate - datetime

    entitytype - string

    entityrefid - uniqueidentifer

    hostname - string

     

    The data contains multiple rows with the same entitytype, entityrefid and a hostname found in the passed in list.

     

    I need to return the most current row (based on createdate) for each unique combination of hostname, entitytype, entityrefid where the createdate is greater than matching passed in date (For example, if the row hostname is 'hostA', then the createdate is greater than hostADate)

     

    What is the best way to pass in the hostnames and to write this select statement.

    Thanks,

    Rick

     

     

     

    Tuesday, June 5, 2007 6:09 PM

Answers

  •  

    Try this Rick:

     

    Code Snippet

    create procedure dbo.hostsrch(

    @xin xml

    )

    as

    begin

    --creating 'MyTable' inline for testing

    declare @mytable table ( refid int identity(1,1), createdate datetime,

    entitytype varchar(20), entityrefid int, hostname varchar(20))

    insert into @mytable values ('02/24/2004', 'type1', 101, 'hosta')

    insert into @mytable values ('04/01/2005', 'type1', 101, 'hosta')

    insert into @mytable values ('05/31/2006', 'type1', 111, 'hosta')

    insert into @mytable values ('02/14/2004', 'type2', 201, 'hostb')

    insert into @mytable values ('12/31/2004', 'type2', 201, 'hostb')

    insert into @mytable values ('06/04/2007', 'type2', 222, 'hostb')

    -- end of inline test code

     

    ;with list as

    (

    select t2.input.value('@hostname','varchar(20)') as hostname,

    t2.input.value('@hostdate', 'datetime') as hostdate

    FROM @xin.nodes('/input/host') as T2(input)

    ), selection as

    (

    select mt2.hostname, mt2.entitytype, mt2.entityrefid, max(mt2.createdate) as createdate

    from @mytable mt2

    inner join list l

    on mt2.hostname = l.hostname

    and mt2.createdate > l.hostdate

    group by mt2.hostname, mt2.entitytype, mt2.entityrefid

    )

    select mt.refid, mt.createdate, mt.entitytype, mt.entityrefid, mt.hostname

    from @mytable mt

    inner join selection sel

    on mt.hostname = sel.hostname

    and mt.entitytype = sel.entitytype

    and mt.entityrefid = sel.entityrefid

    and mt.createdate = sel.createdate

    end

    GO

     

     

    declare @xin xml

    set @xin =

    (

    select *

    from (

          select 'hosta' as hostname, '01/01/2004' as hostdate

    union select 'hostb', '01/01/2004'

    ) as host for xml auto, root('input'), type

    )

     

    exec dbo.hostsrch @xin

     

    Tuesday, June 5, 2007 9:51 PM
  • I hope the XML approach is best FIT for SQL Server 2005,

     

    It is really simple to get the XML data from your DataTable/DataSet object. No iterations sinlge function help you to get the XML data from your Object.

     

    Here I given a code from ADO.NET to SQL Server 2005 Sp

    >> DaleJ -> I adopted your code to accomplish the ADO.NET parsed XML Smile

     

     

    Code Snippet

    --ADO.NET CODE

    DataSet ds = new DataSet();

    ds.DataSetName = "Data";

    ds.Tables.Add("Hosts");

    ds.Tables[0].Columns.Add("HostName");

    ds.Tables[0].Columns.Add("HostDate");

     

    DataRow dr = ds.Tables[0].NewRow();

     

    dr[0] = "HostA";

    dr[1] = "06/06/2007";

    ds.Tables[0].Rows.Add(dr);

     

    dr = ds.Tables[0].NewRow();

    dr[0] = "HostB";

    dr[1] = "06/06/2007";

    ds.Tables[0].Rows.Add(dr);

     

    String xmlParam = ds.GetXml();

     

    //Pass this value to your sp Parameter

     

    ..

    ..

     

      

    Code Snippet

    --SQL Server 2005 SP

    Create procedure dbo.#HostSearch(

                @XML xml

    )

    as

    begin

     

    --creating 'MyTable' inline for testing

    Create table #MyData 

    (

                      refid int identity(1,1)

                    , createdate datetime

                    , entitytype varchar(20)

                    , entityrefid int

                    , hostname varchar(20)

    )

     

    Insert into #MyData values ('02/24/2004', 'type1', 101, 'hosta')

    insert into #MyData values ('04/01/2005', 'type1', 101, 'hosta')

    insert into #MyData values ('05/31/2006', 'type1', 111, 'hosta')

    insert into #MyData values ('02/14/2004', 'type2', 201, 'hostb')

    insert into #MyData values ('12/31/2004', 'type2', 201, 'hostb')

    insert into #MyData values ('06/04/2007', 'type2', 222, 'hostb')

    -- end of inline test code

     

    ;with list as

    (

                select

                            t2.Data.query('./HostName').value('.','varchar(100)') as HostName,

                            t2.Data.query('./HostDate').value('.','Datetime') as HostDate

                FROM @XML.nodes('/Data/Hosts') as T2(Data)

     

    )

    , selection as

    (

                select mt2.hostname, mt2.entitytype, mt2.entityrefid, max(mt2.createdate) as createdate

                from #MyData mt2

                inner join list l

                on mt2.hostname = l.hostname

                and mt2.createdate > l.hostdate

                group by mt2.hostname, mt2.entitytype, mt2.entityrefid

    )

    select

                mt.refid, mt.createdate, mt.entitytype, mt.entityrefid, mt.hostname

    from

                #MyData mt

                inner join selection sel

                on mt.hostname = sel.hostname

                and mt.entitytype = sel.entitytype

                and mt.entityrefid = sel.entityrefid

                and mt.createdate = sel.createdate

    end

     

     

    Code Snippet

    --Sample Exec Query

    Exec #HostSearch

    '<Data>

      <Hosts>

        <HostName>HostA</HostName>

        <HostDate>06/06/2007</HostDate>

      </Hosts>

      <Hosts>

        <HostName>HostB</HostName>

        <HostDate>06/06/2007</HostDate>

      </Hosts>

    </Data>'

     

     

     

    Wednesday, June 6, 2007 7:39 AM

All replies

  • You could use following code:

    Code Snippet

    create procedure SearchHosts

    @searchdate datetime,

    @searchhost varchar(100)

    AS

    select entitytype, entityrefid, hostname, max(createdate)

    from MyTable

    where createdate>@searchdate

    and hostname =@searchhost

    group by entitytype, entityrefid, hostname

     

    I use "group by"  for grouping date for each unique comdination of entitytype, entityrefid, hostname and max function for last createdate in each group

    Tuesday, June 5, 2007 6:48 PM
  • The problem is that there are multiple host and associated hostdates that need to be passed in.

    hostA - hostADate, hostB - hostBDate etc.

    I appreciate your attempt, but I believe your solution only allows for one host and date.

     

    Thanks,

    Rick

    Tuesday, June 5, 2007 7:30 PM
  •  

    Try this Rick:

     

    Code Snippet

    create procedure dbo.hostsrch(

    @xin xml

    )

    as

    begin

    --creating 'MyTable' inline for testing

    declare @mytable table ( refid int identity(1,1), createdate datetime,

    entitytype varchar(20), entityrefid int, hostname varchar(20))

    insert into @mytable values ('02/24/2004', 'type1', 101, 'hosta')

    insert into @mytable values ('04/01/2005', 'type1', 101, 'hosta')

    insert into @mytable values ('05/31/2006', 'type1', 111, 'hosta')

    insert into @mytable values ('02/14/2004', 'type2', 201, 'hostb')

    insert into @mytable values ('12/31/2004', 'type2', 201, 'hostb')

    insert into @mytable values ('06/04/2007', 'type2', 222, 'hostb')

    -- end of inline test code

     

    ;with list as

    (

    select t2.input.value('@hostname','varchar(20)') as hostname,

    t2.input.value('@hostdate', 'datetime') as hostdate

    FROM @xin.nodes('/input/host') as T2(input)

    ), selection as

    (

    select mt2.hostname, mt2.entitytype, mt2.entityrefid, max(mt2.createdate) as createdate

    from @mytable mt2

    inner join list l

    on mt2.hostname = l.hostname

    and mt2.createdate > l.hostdate

    group by mt2.hostname, mt2.entitytype, mt2.entityrefid

    )

    select mt.refid, mt.createdate, mt.entitytype, mt.entityrefid, mt.hostname

    from @mytable mt

    inner join selection sel

    on mt.hostname = sel.hostname

    and mt.entitytype = sel.entitytype

    and mt.entityrefid = sel.entityrefid

    and mt.createdate = sel.createdate

    end

    GO

     

     

    declare @xin xml

    set @xin =

    (

    select *

    from (

          select 'hosta' as hostname, '01/01/2004' as hostdate

    union select 'hostb', '01/01/2004'

    ) as host for xml auto, root('input'), type

    )

     

    exec dbo.hostsrch @xin

     

    Tuesday, June 5, 2007 9:51 PM
  •  

    This is pretty slick and it appears to work. 

    Thanks for the effort in putting together a full working example!

     

    However, your solution requires that I pass in my values as xml, and it isn't clear how I do that.

    Let's say the client is pulling the values from an ADO.Net datatable where each DataRow has a hostname and hostdate column.

    they loop through the table to build the list of hostname/hostdate values.

    What's the best way to go from the datatable/datarow to the xml parameter data as your solution calls for?

     

    Thanks,

    Rick

    Wednesday, June 6, 2007 3:57 AM
  • I hope the XML approach is best FIT for SQL Server 2005,

     

    It is really simple to get the XML data from your DataTable/DataSet object. No iterations sinlge function help you to get the XML data from your Object.

     

    Here I given a code from ADO.NET to SQL Server 2005 Sp

    >> DaleJ -> I adopted your code to accomplish the ADO.NET parsed XML Smile

     

     

    Code Snippet

    --ADO.NET CODE

    DataSet ds = new DataSet();

    ds.DataSetName = "Data";

    ds.Tables.Add("Hosts");

    ds.Tables[0].Columns.Add("HostName");

    ds.Tables[0].Columns.Add("HostDate");

     

    DataRow dr = ds.Tables[0].NewRow();

     

    dr[0] = "HostA";

    dr[1] = "06/06/2007";

    ds.Tables[0].Rows.Add(dr);

     

    dr = ds.Tables[0].NewRow();

    dr[0] = "HostB";

    dr[1] = "06/06/2007";

    ds.Tables[0].Rows.Add(dr);

     

    String xmlParam = ds.GetXml();

     

    //Pass this value to your sp Parameter

     

    ..

    ..

     

      

    Code Snippet

    --SQL Server 2005 SP

    Create procedure dbo.#HostSearch(

                @XML xml

    )

    as

    begin

     

    --creating 'MyTable' inline for testing

    Create table #MyData 

    (

                      refid int identity(1,1)

                    , createdate datetime

                    , entitytype varchar(20)

                    , entityrefid int

                    , hostname varchar(20)

    )

     

    Insert into #MyData values ('02/24/2004', 'type1', 101, 'hosta')

    insert into #MyData values ('04/01/2005', 'type1', 101, 'hosta')

    insert into #MyData values ('05/31/2006', 'type1', 111, 'hosta')

    insert into #MyData values ('02/14/2004', 'type2', 201, 'hostb')

    insert into #MyData values ('12/31/2004', 'type2', 201, 'hostb')

    insert into #MyData values ('06/04/2007', 'type2', 222, 'hostb')

    -- end of inline test code

     

    ;with list as

    (

                select

                            t2.Data.query('./HostName').value('.','varchar(100)') as HostName,

                            t2.Data.query('./HostDate').value('.','Datetime') as HostDate

                FROM @XML.nodes('/Data/Hosts') as T2(Data)

     

    )

    , selection as

    (

                select mt2.hostname, mt2.entitytype, mt2.entityrefid, max(mt2.createdate) as createdate

                from #MyData mt2

                inner join list l

                on mt2.hostname = l.hostname

                and mt2.createdate > l.hostdate

                group by mt2.hostname, mt2.entitytype, mt2.entityrefid

    )

    select

                mt.refid, mt.createdate, mt.entitytype, mt.entityrefid, mt.hostname

    from

                #MyData mt

                inner join selection sel

                on mt.hostname = sel.hostname

                and mt.entitytype = sel.entitytype

                and mt.entityrefid = sel.entityrefid

                and mt.createdate = sel.createdate

    end

     

     

    Code Snippet

    --Sample Exec Query

    Exec #HostSearch

    '<Data>

      <Hosts>

        <HostName>HostA</HostName>

        <HostDate>06/06/2007</HostDate>

      </Hosts>

      <Hosts>

        <HostName>HostB</HostName>

        <HostDate>06/06/2007</HostDate>

      </Hosts>

    </Data>'

     

     

     

    Wednesday, June 6, 2007 7:39 AM
  •  

    Excellent.  Looks perfect Mani!

    Wednesday, June 6, 2007 10:42 AM
  •  

    Had not used xml as a parameter before, it was easier than I thought and your example completes the solution.

     

    Thanks,

    Rick

     

    Wednesday, June 6, 2007 2:10 PM