Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unhappy Unanswered: very complicated stored procedure problem

    Hi, i have to build very complicated stored procedure and i'm not sure it's possible.
    The aim is to create a recordset that has column names taken from one table, row names taken from another table and
    cell values taken from the third table, and i don't know how many columns will be , i'll give a simplified example of tables structure and expected result:

    Table A: holds expected result table id
    A_id

    Table B holds row numbers in expected table and A_id as foreign key:
    B_id A_id row#

    Table C holds Column names
    C_id columnName

    Table D holds values of C which are Cell values for expected table and id of C as foreign key
    D_id cellValue C_id

    And finally E table that connects Cell values with(table D) and row numbers (table B) to connection n:n

    I think if I draw a diagram it would be clearer, but i don't know how to insert it here.

    So, expected result shoul be: if function called draw table (A_id)
    then i find all column names all row numbers and all cell values and insert on it's place....
    I know i should use dynamic sql but i'm writing this stored procedure for two days without results((
    Thank you for any help
    Thank you
    Yulia

  2. #2
    Join Date
    Apr 2004
    Posts
    10
    it might be simpler if you built your queries outside sql, e.g. in vb
    is that an option, or *must* it be a stored procedure?

    what have you done with dynamic sql so far?

  3. #3
    Join Date
    May 2004
    Posts
    27
    im a newbie but how about the simple approach. Create a form like they have in access and use wizard to create and bind the fields for you. After access does this you can cut and paste the code into mysql.


    Here is an example in my attachment
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would never recommend building queries in VB over a stored procedure.

    You will probably need to use dynamic SQL.

    Diagram your table structure and post it here as a file attachment.
    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 2002
    Location
    Jersey
    Posts
    10,322
    What's a row name?

    Show us the DDL of the tables...sample data, and what the expect result should look like...

    And why you need to do this or what you're really trying to accomplish....might be an easier way...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Posts
    33

    Unhappy

    Thank you all for answer. I still didn' succeed to write my stored procedure. I'll attach the file with database diagram and expected result for stored procedure, may be someone can help
    Attached Files Attached Files
    Thank you
    Yulia

  7. #7
    Join Date
    May 2004
    Posts
    27
    my database is on the above attachment. Im decent with vb programming but suck with databases! Basically Ive to accomplish the following. I created code for a calendar that allow a user to enter a value for any date in the calendar. I need to take the values they entered into my vb calendar and save the dates with a value to my database. If I were working with one table it wouldnt be a problem. Unfortunately Im using ADO which only allows for 1 table connection at a time. To compensate for this i used 2 ado connections. 1 for each table. The details table shold store the date of an infraction for the employee(infraction-Date that employee screwed their attendance up by a no-call/no-show, coming in late, etc)

    My problem is if an infraction is entered it might be the first time that employee got an infraction and i get an error because im moving both tables at the same time. I know basically im supposed to use an if exists clause for this and then next time the details page is available then re synchronize the tables according to employee id. My prob is I only know what it says in a book. Ive no practical experience. I guess what is like to see is a very simple
    vb program hooked up to a database with 2 table a main table and a details table. Then I would be fine As i can pick apart the code to see what it does.


    Normally Id just attach my project as a whole but as thier are very complicated calculations in it, Im not yet done with my error checking for these calulations.

    Also from what I do know of databases and structure I currently have my table setup correctly I wanted to verify that at this point and hopefully ...see a sample program of this as stated above.
    Last edited by greg85374; 05-24-04 at 14:43.

  8. #8
    Join Date
    Nov 2003
    Posts
    33
    The main problem is that I don't know how to create stored procedure with unknown number of columns and name each column as a name of record it was taken from.
    An abstract example:
    Table A fields: id, name

    If i needed one column:
    SELECT name
    FROM A
    WHERE name = @a_name

    But I need N columns:
    SELECT ??? (all names)
    FROM A
    WHERE id > 5

    Thank you
    Thank you
    Yulia

  9. #9
    Join Date
    May 2004
    Posts
    27
    [QUOTE=yuliam]The main problem is that I don't know how to create stored procedure with unknown number of columns and name each column as a name of record it was taken from.
    An abstract example:
    Table A fields: id, name

    If i needed one column:
    SELECT name
    FROM A
    WHERE name = @a_name

    But I need N columns:
    SELECT ??? (all names)
    FROM A
    WHERE id > 5

    not really sure hwat youre looking for but try
    Select(*) from tablename
    where NAme <> ""

  10. #10
    Join Date
    Apr 2004
    Posts
    49
    Hi,

    Maybe You Can Try This One Out :-

    Select Distinct Column_name From Information_schema.columns Where Table_name = 'a'

    You Would Be Getting The Column_names You Require From A Particular Table.

    But,
    If You Also Require The Number Of Columns You Can Use

    Select @@rowcount,simultaneously With The Above The Select Statement

    Once You Do This You Would Be Getting The Names Of Columns As A Record In The Particular Table
    You Can Use :-

    Select Distinct Column_name,@@rowcount
    Into #tmp1
    From Information_schema.columns Where Table_name = 'a'

    Then You Can Use This Information As A Loop To Do The Calculation :-

Posting Permissions

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