SQL Server Performance

Need a Stored Procedure Urgently.

Discussion in 'SQL Server 2005 General Developer Questions' started by shahid00704, Dec 14, 2009.

  1. shahid00704 New Member

    I have Five tables and i want to get result from these five tables providing some checks.

    Site
    ----------------------------
    SiteID int,
    SiteTypeID int,
    PowerTypeID int,
    LocationID int,
    SiteCode nvarchar(500)

    Sites_SiteType
    -----------------------------
    ID int,
    Type nvarchar(500)

    Sites_PowerType
    -----------------------------
    ID int,
    Type nvarchar(500)

    SiteTypeRenewal
    -----------------------------
    RenewalID int,
    SiteTypeId int,
    SiteID


    PowerTypeRenewal
    -----------------------------
    RenewalID int,
    PowerTypeId int,
    SiteID


    Site Data
    ----------------
    SiteID SiteTypeID PowerTypeID LocationID SiteCode
    1 10 3 100 xxx
    2 20 1 200 yyy
    3 30 2 300 zzz
    4 30 1 100 xxx


    Sites_siteType Data
    ----------------------------
    ID Type
    10 RoofTop
    20 Transformer
    30 GreenField


    SiteTypeRenewal
    ----------------------------
    RenewalID SiteTypeID SiteID
    1 20 1
    2 30 2
    3 10 3
    4 10 2
    5 20 3

    Sites_PowerType Table Data
    ----------------------------
    ID Type
    1 Generator
    2 T.VStation
    3 Transformer


    PowerTypeRenewal
    ----------------------------
    RenewalID PowerTypeID SiteID
    1 2 1
    2 3 2
    3 1 3
    4 1 2
    5 2 3

    herei want to check in the renewals(SiteTypeRenewal and PowerTypeRenewal)tables if there are records for a particular (SiteID) then extractmaximum(last) record based on SiteID and display Type of Site_siteTypeand Site_PowerType by inner joining from renewal table. here ifSiteID=3, it should give me LocationID=300,SiteCode=zzzSiteType=Transformer and PowerType=T.VStation.
    else check in theSite Table whether that SiteID is available or not if available extractthat record joining Sites_SiteType and Site_PowerType and display it exhere for this if SiteID=4, it should give meLocationID=100,SiteCode=xxx SiteType=GreenField and PowerType=Generator.

    my final result looks like this...

    for SiteID=3
    SiteID LocationID SiteCode SiteType PowerType

    3 300 zzz Transformer T.VStation

    for SiteID=4 which is not available in renewals tables

    SiteID LocationID SiteCode SiteType PowerType

    4 100 xxx GreenField Generator




    i have written procedures for single value extraction.. just for understanding purpose i giving this.

    Alter procedure Proc_Name2
    @siteid int
    as
    begin
    declare @sid int


    if exists(select SiteID from Site_Renewal_SiteType where SiteID=@siteid)
    select top 1 SSRT.SiteTypeRenewalID,Site_SiteTypes.Type from Site_SiteTypes where SSRT.SiteID=@siteid INNER JOIN
    Site_Renewal_SiteType SSRT ON Site_SiteTypes.ID = SSRT.SiteTypeID INNER JOIN
    New_Site ON SSRT.SiteID = New_Site.SiteID
    order by SSRT.SiteTypeRenewalID desc
    else if exists(select SiteID from New_Site where SiteID=@siteid)
    select SiteID from New_Site
    else
    select 'N/A'
    end
    go





    Alter procedure Proc_Name3
    @siteid int
    as
    begin
    if exists(select SiteID from Site_Renewal_PowerType where SiteID=@siteid)
    select top 1 SSPT.PowerTypeRenewalID,Site_PowerTypes.Type from Site_PowerType where SSPT.SiteID=@siteid INNER JOIN
    Site_Renewal_PowerType SSPT ON Site_PowerType.ID = SSPT.PowerTypeID INNER JOIN
    New_Site ON SSPT.SiteID = New_Site.SiteID
    order by SSPT.PowerTypeRenewalID
    else if exists(select SiteID from New_Site where SiteID=@siteid)
    select SiteID from New_Site
    else
    select 'N/A'
    end
    go


    from these two procedures i can get single column.

    but i want all 5 Columns which is present in Site Table should display based on renewals tables.

    please if any know this.. kindly reply.

  2. Adriaan New Member

    First option is to do a UNION ALL on one query that returns results from SiteTypeRenewal based on your given criteria, and a second query that returns results from PowerTypeRenewal again based on your given criteria.
    Second option is to do a full join on SiteTypeRenewal and PowerTypeRenewal (on SiteID) and outer joins on all tables from which you will need the lookup values. Then use CASE statements for the column expressions, comparing the renewal details from both fully joined tables, and returning the column from the appropriate table.
  3. shahid00704 New Member

    Hello Adriaan.
    i am unable do this. Since there are two procedures i am unable to join these two procedures.
    Please give the example code to write this. so that i can try for it.
    Hope to get a reply from u.
    Thanks.
  4. Adriaan New Member

    UNION [ALL] is a keyword that you can put between two queries. This will combine the results of the two queries in one single resultset.
    The only requirements are that the number of columns are the same, that the columns are in the same order, and that the data types of each set of columns match.
    So you set up one query for the results from SiteTypeRenewal, with the criteria that would return the relevant results from this table and from any joined tables in this first query.
    Next you set up another query for the results from PowerTypeRenewal, again with the criteria that would return the relevant results from this table and from any joined tables in this second query.
    Just put UNION ALL between the two queries in your script. It doesn't matter that one of the queries doesn't return any results - the other query will.
  5. shahid00704 New Member

    I have written here like this... But it is giving me error.. Incorrect syntax near the keyword 'if'.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER procedure [dbo].[Proc_Name2]
    @siteid int
    as
    begin
    declare @sid int

    if exists(select SiteID from Site_Renewal_SiteType where SiteID=@siteid)
    select top 1 SiteTypeRenewalID,SiteTypeID,SiteID from Site_Renewal_SiteType where SiteID=@siteid order by SiteTypeRenewalID desc
    else if exists(select SiteID from New_Site where SiteID=@siteid)
    select SiteID from New_Site
    else
    select 'N/A' as 'SiteType'

    Union All

    if exists(select SiteID from Site_Renewal_PowerType where SiteID=@siteid)
    select top 1 PowerTypeRenewalID,PowerTypeID,SiteID from Site_Renewal_PowerType where SiteID=@siteid order by PowerTypeRenewalID desc
    else if exists(select SiteID from New_Site where SiteID=@siteid)
    select SiteID from New_Site
    else
    select 'N/A' as 'PowerType'


    end
    Please reply..
    i want something
    Create Procedure RenewalsBased
    @SiteID int
    as
    Select SiteID,LocationID,SiteCode,
    (if exists(select SiteID from Site_Renewal_SiteType where SiteID=@SiteID)
    Select Site_SiteTypes.Type
    FROM Site_SiteTypes INNER JOIN
    Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
    New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
    WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
    (SELECT MAX(SiteTypeRenewalID) AS Expr1
    FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 where Site_Renewal_SiteType_1.SiteID = @SiteID))
    else
    SELECT Site_SiteTypes.Type
    FROM New_Site INNER JOIN
    Site_SiteTypes ON New_Site.SiteType = Site_SiteTypes.ID
    WHERE (New_Site.SiteID = @SiteID)),
    (if exists(select SiteID from Site_Renewal_PowerType where SiteID=@SiteID)
    Select Site_PowerTypes.Type
    FROM Site_PowerTypesINNER JOIN
    Site_Renewal_PowerTypesON Site_PowerTypes.ID = Site_Renewal_PowerTypes.PowerTypeID
    WHERE (Site_Renewal_PowerTypes.PowerTypeRenewalID =
    (SELECT MAX(PowerTypeRenewalID ) AS Expr1
    FROM Site_Renewal_PowerTypes AS Site_Renewal_PowerTypes_1 where Site_Renewal_PowerTypes_1.SiteID =@SiteID))
    else
    SELECT Site_PowerTypes.Type
    FROM New_Site INNER JOIN
    Site_PowerTypesON New_Site.PowerTypes= Site_SiteTypes.ID
    WHERE (New_Site.SiteID = @SiteID)) from New_Site where SiteID=@SiteID




    but it is giving error near if condition saying that error near if.
  6. Adriaan New Member

    Don't use control-of-flow syntax (IF) here.
    Write a straightforward query that returns the results from SiteTypeRenewal with its joined tables. Include a WHERE clause that ensures that only the proper results are returned.
    Then write another straightforward query that returns the results from PowerTypeRenewal with its joined tables. Again include a WHERE clause that ensures that only the proper results are returned.
    Then put UNION ALL between those two queries.
  7. shahid00704 New Member

    Here if i dont use control of flow IF then how can i check whether there is a record in renewals tables??? If there is no record in renewals table then i want to extract it from Site Table... So If is necessary there i think.
  8. Adriaan New Member

    Use the WHERE NOT EXISTS (correlated subquery) syntax.
  9. shahid00704 New Member

    If i use Where Not exist it gives only records which are not available in renewals tables... but if there are records in Renewals table then i want that record based on max and siteID condition.
    Have a look Here...
    (if exists(select SiteID from Site_Renewal_SiteType where SiteID=@SiteID)
    if there is record in Renwals table I am extracting it from renewals based on my criteria
    Select Site_SiteTypes.Type
    FROM Site_SiteTypes INNER JOIN
    Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
    New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
    WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
    (SELECT MAX(SiteTypeRenewalID) AS Expr1
    FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 whereSite_Renewal_SiteType_1.SiteID = @SiteID))
    Now Here Else is for Retriving record from Site Table...i.e, if there is no record in Renewals then iam extracting it from Site Table. I am extracting in my both conditions if available in renewals extract else extract from site table.
    SELECT Site_SiteTypes.Type
    FROM New_Site INNER JOIN
    Site_SiteTypes ON New_Site.SiteType = Site_SiteTypes.ID
    WHERE (New_Site.SiteID = @SiteID))
    I think u got my point now. And also if i use Union all i will get data in the form of rows not like columns....
    i want
    SiteType PowerType
    Greenfield Generator
    union all gives like this
    No Columns
    GreenField
    Generator
    which i dont want.
  10. Adriaan New Member

    I've already outlined what you need to do. The whole point is that you need to stop thinking in IF ... ELSE ... logic.
    To get started, write a simple query that reads the results from Site_SiteTypes and any tables you want to join on it, and add the WHERE clause for SiteID.
    To this simple query, at the end of the WHERE clause, add
    AND NOT EXISTS (subquery)
    ... this subquery is one that tries to find rows in the other source table, given the same criteria.
    The NOT EXISTS keyword makes sure that it will only show rows from this table if there is no match in that table.
    Now repeat this for the other source table, with a NOT EXISTS subquery that tries to find rows in Site_SiteTypes.
    Put these two queries together, one after the other, and stick UNION ALL between them.
  11. shahid00704 New Member

    Dear,
    According to u i wrote this query.
    It is giving output only if there is no record in Renewals table and if there is any record then it is not showing that record.
    Alter procedure proced
    @SiteID int
    as
    Begin
    SELECT Site_SiteTypes.Type as SiteType
    FROM New_Site INNER JOIN
    Site_SiteTypes ON New_Site.SiteType= Site_SiteTypes.ID
    where SiteID=@SiteID and Not Exists(Select Site_SiteTypes.Type as SiteType
    FROM Site_SiteTypes INNER JOIN
    Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
    New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
    WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
    (SELECT MAX(SiteTypeRenewalID) AS Expr1
    FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 where Site_Renewal_SiteType_1.SiteID =@SiteID)))
  12. Adriaan New Member

    Well, it's starting to look promising ... but I would have expected the NOT EXISTS subquery to refer to the Power*** tables, not the Site*** tables themselves.
    Also I would have expected to see more columns for the resultset.
    And this would be the FIRST of TWO queries. The second query would read be exactly like this one, but with the main part reading columns from the Power*** tables, with a NOT EXISTS subquery referring to the Site*** tables.
    So you would have those two rather complicated queries, and you would simply stack the resultsets by adding UNION ALL between the two queries.
  13. Adriaan New Member

    Just as an example:SELECT a.Site, a.AID FROM tableA a WHERE NOT EXISTS (SELECT b.BID FROM tableB b WHERE b.BID = a.AID)
    UNION ALL
    SELECT b.Site, b.BID FROM tableB B WHERE NOT EXISTS (SELECT a.AID FROM tableA a WHERE a.AID = b.BID)
  14. renuka New Member

    /* create procedure*/create procedure site_info@siteid int
    as
    beginselect s.siteid,s.locationid,s.sitecode,s.SiteTypeID
    ,case when sst.[type]is null then st.[type] else sst.[type] end as sitetype,s.PowerTypeID
    ,case when spt.[type] is null then pt.[type] else spt.[type] end as powertypefrom site as s
    left join (select siteid,max(sitetypeid)as sitetypeidfrom SiteTypeRenewal
    group by siteid)as sr on s.siteid = sr.siteidleft join Sites_SiteType as sst on sr.sitetypeid = sst.id
    left join (select siteid,max(powertypeid)as powertypeidfrom PowerTypeRenewal
    group by siteid)as pr on s.siteid = pr.siteidleft join Sites_PowerType as spt on spt.id = pr.powertypeid
    LEFT JOIN Sites_SiteType as st on s.sitetypeid = st.idleft join Sites_PowerType as pt on pt.id = s.powertypeid
    where s.siteid = case when @siteid is null then s.siteid else @siteid endend/*execute*/exec
    site_info 3exec
    site_info nullexec
    site_info 4

Share This Page