Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question Unanswered: Equivalent of VB MsgBox function?

    Hi,

    I'd like to perform an update on a database only when certain conditions are met. Hence, one of those conditions would be a positive answer from the user running the query. Basically, I'd like to display a message with a yes/no option for the user to choose from. If and only if the user user selects the YES option should we perform the update.

    Inside a transact SQL "IF ... THEN", how can I achieve this?

    Thanks,

    SC

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use a message box (assuming you are writing your application in VB)? You can't do it within Transact-SQL. That is as it should be, since user interaction is a client side issue, not a server side issue.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i have a script at home somewhere that someone sent me that asks you questions one at a time in the query analyzer.

    it used a stored procedure and during operation asked for input in the QA.
    I forget what it was supposed to do but it worked and quite well.

    i'll try to find it.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    if @whatusersays = 'yes' then
    begin
    doSomeWork
    end
    else
    begin
    doSomethingElse
    end

    What do you mean with "Inside a transact SQL.."?

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    humm... it must be friday.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i mean that it is not possible to launch a msg box directly from Transact SQL
    however, you can stretch all kind of possibilities into anything you want

    run an sp from the query analyzer
    calls a vbscript that runs msgbox()
    delivers the param to sql server in dynamic sql
    sql runs proc with choice.

    "youll be building wrappers just like wrigleys"

    it might just be easier to build the logic in your app
    ask the question there and then call the proc with the right parameter
    runs once.

    much simpler.

  7. #7
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    OK.

    I understand that user interaction should be in the app, on the client side. However this code tidbit I'm trying to write will always be used directly in query analyzer for the manual triggering of modifications to the db. Problem is that I don't trust the people that will run it so I prefer to put as much "protection" in the code as possible to minimize chances of errors.

    Although I was expecting a function directly in Transact SQL to do this, I guess I can live with calling a sp that references a VB script or another outside procedure.

    Thanks for your help,

    Skip.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I want to see how this works when the Query Analyzer is on a different machine than the SQL Server! This could be fun!

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just use an Access data Project....

    You should not be using QA as a User Interface....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i cant find that script anywhere!
    it was cool, basically you ran the script in the QA and it prompted you (probably PRINT) for a value. you enter the value next to the name of the sp at the top of the script and it enters and then prompts you again
    it did this about 3 or 4 times and then you get your output.
    now this may have been a temp table and the sp asks for the next parameter based on an if statment and the config of the temp table.

    etc etc etc.

    I thought that i had posted it here a while back.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I remember that too. But I can't remember how it was done.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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