Professional Web Applications Themes

New Stored Procedure. Help me to write it. - Microsoft SQL / MS SQL Server

I must to implent a sp that checks data into a one to one relationed table and let me know if all data is available or not. To better understand (sorry for my poor english) imagine this. TABLE 1 ID DESCR QTA 1 aaa 1 2 bbb 1 TABLE 2 ID DESCR REALQTA 1 aaaa 4 2 bbbb 1 Because data in table 2 is added only in a certain moment of app live, may be that table 2 doesn't contain any row related to table 1 or just an half or something like it. I want to do an ...

  1. #1

    Default New Stored Procedure. Help me to write it.

    I must to implent a sp that checks data into a one to one relationed
    table and let me know if all data is available or not.

    To better understand (sorry for my poor english) imagine this.

    TABLE 1

    ID DESCR QTA
    1 aaa 1
    2 bbb 1

    TABLE 2

    ID DESCR REALQTA
    1 aaaa 4
    2 bbbb 1

    Because data in table 2 is added only in a certain moment of
    app live, may be that table 2 doesn't contain any row related
    to table 1 or just an half or something like it.

    I want to do an sp that say me if row into table1 and table 2
    match per number, and that realqta is filled and not an empty
    or a negative number (I must to decide in which way recognize
    when data is present).

    I hope that someone can help me.

    --
    Sincerely
    Andrea Moro


    Andrea Moro Guest

  2. #2

    Default Re: New Stored Procedure. Help me to write it.

    >I want to do an sp that say me if row into table1 and table 2
    >match per number,
    (im not sure what does per number mean)

    declare x int
    delcare y int
    select x=count(*) from table1
    select y=count(*) from table2

    x and y variables will contain the total number of rows exists in
    respective tables.
    >and that realqta is filled and not an empty or a negative number (I must to
    decide in which way recognize
    >when data is present).
    following query will return the rows which has either realqta null or has <
    0 value

    select * from table2
    where (realqta is null or realqta < 0)

    --
    -Vishal
    "Andrea Moro" <moroandreaETtiscalinet.it> wrote in message
    news:e9x#FwkQDHA.3192TK2MSFTNGP10.phx.gbl...
    > I must to implent a sp that checks data into a one to one relationed
    > table and let me know if all data is available or not.
    >
    > To better understand (sorry for my poor english) imagine this.
    >
    > TABLE 1
    >
    > ID DESCR QTA
    > 1 aaa 1
    > 2 bbb 1
    >
    > TABLE 2
    >
    > ID DESCR REALQTA
    > 1 aaaa 4
    > 2 bbbb 1
    >
    > Because data in table 2 is added only in a certain moment of
    > app live, may be that table 2 doesn't contain any row related
    > to table 1 or just an half or something like it.
    >
    > I want to do an sp that say me if row into table1 and table 2
    > match per number, and that realqta is filled and not an empty
    > or a negative number (I must to decide in which way recognize
    > when data is present).
    >
    > I hope that someone can help me.
    >
    > --
    > Sincerely
    > Andrea Moro
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Solution

    Here is the stored procedure to do the requested work.
    It use a correlated subquery.


    Select Table1ID=Table1.id,
    CASE T1T2Match
    WHEN exists(select table2.id from table2 where
    table2.id=t1.id)
    THEN TRUE
    ELSE
    FALSE
    END,

    CASE realqta_Valid
    WHEN exists(select realqta from table2
    where table2id=t1.id
    and realqta>0
    and realqta<>NULL)
    THEN TRUE
    ELSE
    FALSE
    END

    FROM table1 as t1

    _____
    Because it uses a correlated subquery the result will show
    all table1 ID wheter they have a match or not.
    The second column will indicate a match or not with table2.
    The third column will say if realqta is valid.
    Realqta will always be invalid when there is no match found
    Result:
    Table1ID t1t2match realqta_Valid
    1 TRUE TRUE
    2 FALSE FALSE

    Hope this helps.
    >-----Original Message-----
    >I must to implent a sp that checks data into a one to one
    relationed
    >table and let me know if all data is available or not.
    >
    >To better understand (sorry for my poor english) imagine
    this.
    >
    >TABLE 1
    >
    >ID DESCR QTA
    >1 aaa 1
    >2 bbb 1
    >
    >TABLE 2
    >
    >ID DESCR REALQTA
    >1 aaaa 4
    >2 bbbb 1
    >
    >Because data in table 2 is added only in a certain moment
    of
    >app live, may be that table 2 doesn't contain any row
    related
    >to table 1 or just an half or something like it.
    >
    >I want to do an sp that say me if row into table1 and
    table 2
    >match per number, and that realqta is filled and not an
    empty
    >or a negative number (I must to decide in which way
    recognize
    >when data is present).
    >
    >I hope that someone can help me.
    >
    >--
    >Sincerely
    >Andrea Moro
    >
    >
    >.
    >
    Laurent Lemire Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  3. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  4. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  5. Stored procedure from stored procedure
    By Red Valsen in forum Informix
    Replies: 3
    Last Post: October 2nd, 02:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139