Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Question Unanswered: t-sql: returning a row with check

    Hello all,

    i am writing a SP and want to do the next thing:

    1. check whether a row exists by key field.
    2. if not - raiserror
    3. if does exist - return it.

    the thing is that i dont want to scan the table twice with 2 select statements, and dont know how to do it....

    any sugestions ??

    thanks a lot, Ran.

  2. #2
    Join Date
    Apr 2004
    Posts
    2

    Re: t-sql: returning a row with check

    sorry, did a mistake:
    in step 3, i might want to return only one field of the row (in some cases).

    thanks again, Ran.

    Originally posted by ranyc
    Hello all,

    i am writing a SP and want to do the next thing:

    1. check whether a row exists by key field.
    2. if not - raiserror
    3. if does exist - return it.

    the thing is that i dont want to scan the table twice with 2 select statements, and dont know how to do it....

    any sugestions ??

    thanks a lot, Ran.

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT col1, col2
    FROM table1
    WHERE col1 = '1'

    IF @@ROWCOUNT = 0
    BEGIN
    RAISERROR ('Exercise in futility.',16,1)
    RETURN 0
    END
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    declare @field_key your_data_type (..)
    set @field_key = (select field_key from your_table
    where col1 = '1' --don't know what this is
    if @field_key is null raiserror ('your_error, 16, 1)
    return @field_key

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you do it that way, then:

    <i>in step 3, i might want to return only one field of the row (in some cases).</I>

    you will only be able to return one row.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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