HELP!---Stored Procedure in ASP

Discussion in 'ASP General' started by segis bata, Apr 3, 2006.

  1. segis bata

    segis bata Guest

    Hello everyone,

    I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
    1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
    2.. Insert some other info in another table (tblX03)
    With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?

    Thanks for your prompt response,
    SB-R
    segis bata, Apr 3, 2006
    #1
    1. Advertising

  2. segis bata wrote:
    > Hello everyone,
    >
    > I'm writing you because I need help in something that's taking too
    > long. I want to build a Stored Procedure to be accessed by an ASP
    > page and I need that Stored Procedure to do a couple of things:
    > 1.. Depending on parameters sent by the ASP page, do a SELECT
    > statement to a table (tblX01) and if it returns ZERO records, the SP
    > should insert some info into other table (tblX02) and if returns ONE
    > record, it won't insert into tblX02
    > 2.. Insert some other info in another table (tblX03)
    > With #2 I don't have a problem, is with #1, how do I know the select
    > statement returns ZERO or ONE record? how do I capture that value in
    > the Stored Procedure?
    >

    IF EXISTS (SELECT * FROM tblX01 WHERE ...)
    BEGIN
    INSERT INTO tblX02 ...
    END


    HTH,
    Bob Barrows

    PS. Please don't crosspost to so many groups. sqlserver.programming was the
    only really relevant group, although .asp.db would have been all right as
    well, despite the fact that the question was answerable without even
    mentioning asp.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 3, 2006
    #2
    1. Advertising

  3. on 1. you can use the following

    if (SELECT count(*) from tblX01 where [your condition]) = 0
    insert into tblX02

    you can add the 2. part after it

    Let me know if it answers what you asked...

    "segis bata" wrote:

    > Hello everyone,
    >
    > I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
    > 1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
    > 2.. Insert some other info in another table (tblX03)
    > With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?
    >
    > Thanks for your prompt response,
    > SB-R
    Edgardo Valdez, MCSD, MCDBA, Apr 3, 2006
    #3
  4. segis bata

    Jack Jackson Guest

    On Mon, 3 Apr 2006 13:37:17 -0400, "segis bata"
    <> wrote:

    >Hello everyone,
    >
    >I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
    > 1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
    > 2.. Insert some other info in another table (tblX03)
    >With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?


    If you don't care how many records exist:

    IF NOT EXISTS(SELECT ?? FROM TABLE ....)
    INSERT ....
    ENDIF

    If you need to know how many exist:

    DECLARE @cnt int
    SELECT @cnt = COUNT(*) FROM ...
    Jack Jackson, Apr 3, 2006
    #4
  5. Jack Jackson wrote:
    > If you don't care how many records exist:
    >
    > IF NOT EXISTS(SELECT ?? FROM TABLE ....)
    > INSERT ....
    > ENDIF
    >

    ??
    Are you sure you are talking about SQL Server?
    True, the OP did not mention which database he was using, but judging from
    the newsgroups in his crosspost ...

    To the OP: it is always a good idea to tell us the type and version of
    database you are using ...
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 3, 2006
    #5
  6. segis bata

    Jack Jackson Guest

    On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
    <> wrote:

    >Jack Jackson wrote:
    >> If you don't care how many records exist:
    >>
    >> IF NOT EXISTS(SELECT ?? FROM TABLE ....)
    >> INSERT ....
    >> ENDIF
    >>

    >??
    >Are you sure you are talking about SQL Server?
    >True, the OP did not mention which database he was using, but judging from
    >the newsgroups in his crosspost ...
    >
    >To the OP: it is always a good idea to tell us the type and version of
    >database you are using ...


    I, like you, assumed the poster wanted a SQL Server stored procedure.
    My code is exactly like yours, except yours did not say NOT EXISTS
    which is what I think he wanted.

    Or maybe you thought I meant to actually put the ?? in the SELECT
    statement - I didn't, I intended that to be a place where he would put
    some field name.
    Jack Jackson, Apr 4, 2006
    #6
  7. Jack Jackson wrote:
    > On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
    > <> wrote:
    >
    >> Jack Jackson wrote:
    >>> If you don't care how many records exist:
    >>>
    >>> IF NOT EXISTS(SELECT ?? FROM TABLE ....)
    >>> INSERT ....
    >>> ENDIF
    >>>

    >> ??
    >> Are you sure you are talking about SQL Server?
    >> True, the OP did not mention which database he was using, but
    >> judging from the newsgroups in his crosspost ...
    >>
    >> To the OP: it is always a good idea to tell us the type and version
    >> of database you are using ...

    >
    > I, like you, assumed the poster wanted a SQL Server stored procedure.
    > My code is exactly like yours, except yours did not say NOT EXISTS
    > which is what I think he wanted.

    :)
    Exactly? Look again. I do not believe the "NOT" keyword is the difference I
    am talking about. :)
    And yes, you are right: I did neglect to put in the "NOT" keyword, but at
    least my code would compile :)

    >
    > Or maybe you thought I meant to actually put the ?? in the SELECT
    > statement - I didn't, I intended that to be a place where he would put
    > some field name.


    1. That's only one of the differences.
    2. Using a field name is totally unnecessary. Granted, it won't harm
    anything - SQL Server ignores the select clause when performing an EXISTS
    subquery.

    Look, I'm not trying to put you on the defensive, or start an argument. I
    just thought you might get a chuckle when you spotted the error I was
    talking about. (I've made the same mistake, especially after attempting to
    write t-sql code after an extended period writing VB - although your mistake
    looks a little more pascal-ish)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 4, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TJS
    Replies:
    4
    Views:
    2,112
  2. Jeff Thur
    Replies:
    2
    Views:
    1,012
    Guest
    Feb 7, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,280
    Mike P
    Jun 19, 2006
  4. Bill Kellaway

    ASP / Stored Procedure SQL Insert Help

    Bill Kellaway, Oct 10, 2003, in forum: ASP General
    Replies:
    7
    Views:
    204
    Bill Kellaway
    Oct 12, 2003
  5. Mike D

    Stored procedure from asp help

    Mike D, Jan 14, 2005, in forum: ASP General
    Replies:
    6
    Views:
    146
    Aaron [SQL Server MVP]
    Jan 17, 2005
Loading...

Share This Page