Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Angry Unanswered: Need help with is CraZZy Stored Proc

    Alright guys, I have been breaking my head on this one for a couple of days and it's to a point where It doesn't make any more sense. I'm trying to avoid using Temp tables with this because it will be called often and by many users. I have a SQL 2K DB and my stored procedure is doing a LEFT JOIN on about 12 tables with 18,000 in most - it is however expected to grow dramatically fast. I am new to stored procedures, and that is probably the whole problem.

    What I have put together is either very slow(minutes) or doesn't display the right info. Also If I use nested selects I am not allowed by SQL to use variables I pass to my SP, is that for a reason?

    here is some stuff I tried
    SP example 1
    SP example 2

    If someone can point in me in the right direction, I would appreciate it greatly, in this situation shoud I be using a #Temp table????

    Thanks in advance,

    --Lito

    Table structure...

    ctsIndex
    --------------------------------------
    * id - int (auto generated)
    * guid - varchar
    * photo_reg - varchar
    * photo_thumb - varchar
    * status - tinyint
    * notes - varchar
    * author_id - int
    * entry_date - datetime

    ctsJrn_[XXXXXX] - there are 8 tables of this kind that are all very similar in structure
    --------------------------------------
    * id - int (auto generated)
    * id_fk - int (clustered index)
    * jrn_entry - (int, varchar) (usually only column that's different between tables)
    * jrn_date - datetime
    * author_id - int
    * entry_date - datetime

    tblComany
    --------------------------------------
    * compID - int (auto generated)
    * compName - varchar
    * compAddress - varchar
    * compCity - varchar
    .... etc

    tblUser
    --------------------------------------
    * usrID - int (auto generated)
    * usrName - varchar
    * usrPass - varchar
    * usrFName - varchar
    ... etc

    tblComUsers
    --------------------------------------
    * ID - int (auto generated)
    * UID - int [user id]
    * CID - int [company id]
    * status - tinyint


    My objective is to generate a record set only for the users that are a part of the company and that have permission to see those records, and also need ability to be able to page through records - could be different number of records for different employees(permissions) of the same company.

    Returned Record set
    --------------------------------------
    id
    guid
    photo_reg
    photo_thumb
    status
    notes
    jrn_entry1
    jrn_date1
    jrn_entry2
    jrn_date2
    jrn_entry3
    jrn_date3
    jrn_entry ... there are 8 total similar tables
    jrn_date ... there are 8 total similar tables

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe this will get you started.

    TOP @eID is invalid, because TOP cannot take a variable as a parameter.

    Use LEFT OUTER JOINs to make sure that all your outer records are returned.

    Not sure what you are trying to do, but I don't think your nested queries were necessary.

    -------------------------------------------------------
    CREATE PROCEDURE cts_GetAnimal2 (@eID INT) AS
    BEGIN

    SELECT TOP 1 --Why are you limiting your result set to one record?
    ctsIndex.id,
    guid,
    stsLabel,
    photo_reg,
    photo_thumb,
    notes,
    bng_id,
    bd_date,
    clr_color,
    ert_no,
    loc_id,
    own_type,
    usrLogin,
    compName,
    own_pct,
    sex_val,
    usid_no
    FROM ctsIndex
    inner join ctsJrn_Owner on ctsIndex.id = ctsJrn_Owner.eID
    LEFT OUTER JOIN ctsJrn_Bangs ON ctsJrn_Bangs.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Birth ON ctsJrn_Birth.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Color ON ctsJrn_Color.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Eartag ON ctsJrn_Eartag.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Location ON ctsJrn_Location.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Owner ON ctsJrn_Owner.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_Sex ON ctsJrn_Sex.eID = ctsIndex.id
    LEFT OUTER JOIN ctsJrn_UsaId ON ctsJrn_UsaId.eID = ctsIndex.id
    LEFT OUTER JOIN tblStatusCodes ON tblStatusCodes.stsID = ctsIndex.status
    LEFT OUTER JOIN tblCompany ON tblCompany.compID = ctsJrn_Owner.own_ID
    LEFT OUTER JOIN tblUser ON tblUser.usrID = ctsJrn_Owner.own_ID
    WHERE ctsIndex.id
    and ctsJrn_Owner.own_ID=38
    ORDER BY ctsIndex.entry_date,
    ctsJrn_Birth.bd_refDate,
    ctsJrn_Color.clr_refDate,
    ctsJrn_Eartag.ert_refDate,
    ctsJrn_Location.loc_refDate,
    ctsJrn_Owner.own_refDate,
    ctsJrn_Sex.sex_refDate,
    ctsJrn_UsaId.usid_refDate
    END
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2003
    Posts
    46
    that's why it is so crazzzzzy...
    - I am limiting it to 1 record because those journals (ctsJrn_Name) could return multiple records for same foreign key, but I need the most recent one sorted by reference date. Also it is suppose to act as a pagination script, one record per page.

    - the nested statements would make sure that only a specific user + company could see that record

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This won't act as a pagination script.

    You should be able to limit records to a given employee using the inner join on ctsJrn_Owner.

    I think you are getting confused by trying to build this query all at once. Start out with a query that just returns the base recordset you want (use the LEFT OUTER JOINS). Then add additional links or criteria to limit records to those with permission to see them.

    Also, using temp tables won't be a problem as long as each user has a separate connection. And you could always use table variables instead (they are often more efficient).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2003
    Posts
    46
    Thanks blindman,
    I was definetly making it harder then I should have been. Sometimes you just have to step back at times and look at the big picture.

    --Lito

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •