Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: Select into local variable within a IF NOT EXIST test?

    Hi all,

    I'm trying to be slick, but so far am just all wet *snicker*

    I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.

    For argument's sake, my PIndex table looks like:
    PID int
    CreateDate smalldatetime
    CloseID float

    Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...

    DECLARE @CloseID float

    IF NOT EXISTS (SELECT @CloseID = CloseID
    FROM PIndex
    WHERE ((PID = '14') and
    (CreateDate = '2004-02-06')))
    SET @CloseID = 100

    The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.

    The trouble is, that it fails to compile with the following error:
    >>>>> Line 3: Incorrect syntax near '='.

    Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.

    Thanks!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Declare @var As Integer
    Declare @param As Integer
    Set @param = x
    If NOT (select count(*) from table where column = @param) = 0
    Begin
    Set @var = @param
    End
    Else
    Set @var = -1
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks for the reply!

    However, if I read your code correctly, one of the basic assumptions it represents is that I know the value in the column I am selecting...and alas, that is not the case in my scenario.

    Using your code to further highlight what I am trying to accomplish, I need to load the value of column into my local variable @param. I'd just prefer not to do a separate select if there is any way around it.

    I know I COULD do the following to accomplish what I need:

    IF NOT EXISTS (SELECT * FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06')))
    SET @CloseID = 100
    ELSE
    SELECT @CloseID = CloseID FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06'))

    I'd just like to do it in one query, if possible.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Declare @var As Integer
    Select @var = ISNULL(column, 100) from table where (condition)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I haven't tried this but I belive it would work:
    Code:
    SELECT @CloseID = CASE 
      WHEN CloseID IS NULL THEN 100
      ELSE CloseID
      END
    FROM PIndex 
    WHERE ((PID = '14') and (CreateDate = '2004-02-06')))
    I'm aslo suspectiong that you might get the same error as in the first post with the Incorrect syntax near '=' and I belive this to be because you have single quotes around PID (PID = '14') and something tells me that PID is a number of some sort...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks, Frettmaestro - That seems to work partially...perhaps I need to fill in a few blanks though.

    It works if the row exists, but the value is null, but doesn't seem to work if the row doesn't exist at all (which is the specific situation I am testing for) - I apologize that I didn't express that better earlier.

    I got it to work though, by merely pre-setting the value of CloseID to 100 prior to the select.

    If there's another way around it, that would be nice to know, but I sincerely appreciate all your input, guys...I now how to use CASE, which I have never used before!

    Oh, and of course you are right about the numeric value and the quoted match string... *blush* I actually use a variable containing the target PID and CreateDate...just thought I'd save some space in the post by using literals to s'plain my problem.

    Thanks again, you guys are great!

    I also belong to a couple classic-car-related boards, and it never ceases to amaze me how the internet has made it so easy to amass a collection of brains that pretty much can help one through any obstacle!

    Collectively, WE ROCK!!!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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