Results 1 to 10 of 10

Thread: pseudocode...

  1. #1
    Join Date
    Jul 2003
    Posts
    45

    Unanswered: pseudocode...

    im trying to code the following but not sure how to do it in sql (not sure on the syntax for the 'if' construct:

    if (dob = NULL) then
    insert [list of fields] from [tablename]
    else
    insert * from [tablename]
    end if

    any hints anyone? thanx!
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) Prefer "if dob is null"
    2) If dob is a variable, it should be @dob. Or do you think about the DBO ?
    3) if you want to use dynamic sql, you must use sp_sqlexec
    4) your insert statment is not complete (I know, it's pseudo-code). What do you really want to insert ?

  3. #3
    Join Date
    Jul 2003
    Posts
    45
    When u say use sp_sqlexec what exactly do you mean?

    Thanks
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  4. #4
    Join Date
    Jul 2003
    Posts
    45
    Just checked on BOL, sp_sqlexe no longer exists.......
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  5. #5
    Join Date
    Jul 2003
    Posts
    45

    Re: pseudocode...

    Take the following:
    insert * from tablename

    Is there a clause which enables me to insert all fields but one,rather than specifiying each of the fields by name and omitting the one i dont want?
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    No.

    you have the syntax

    Code:
    insert into maTable values(all your fields)
    or
    Code:
    insert into maTable (col1, col3, col5) values(val1, val3, val5)

  7. #7
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Originally posted by achowe
    Just checked on BOL, sp_sqlexe no longer exists.......
    Try sp_execsql or maka a seach in your catalog of stored procedures with NAME LIKE "sp%exec"

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    achowe,

    It is good practice to always enumerate your fields in an insert statement. It makes your code easier to read and debug, and also makes it more robust. I never use "select * into".

    blindman

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about using a case statement instead of all that messy dynamic SQL:

    insert into TargetTableName
    select field1,
    field2,...
    case when dob is null then null else (alternative value) end,
    field 3,
    morefields..
    more_case_statments.
    from tablename

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about using a case statement instead of all that messy dynamic SQL:

    insert into TargetTableName
    select field1,
    field2,...
    case when dob is null then null else (alternative value) end,
    field 3,
    morefields..
    more_case_statments.
    from tablename

Posting Permissions

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