Professional Web Applications Themes

Procedure returns first EXEC not last? - Microsoft SQL / MS SQL Server

Hi, I want to create a procedure that checks whether a server is updated or not. I compare a number on the client with a number on the server - the one with the smallest number is not updated. Here is some code from my procedure: ------------------------------------------------------------ DECLARE insync_local int, insync_server int, external_datasource varchar(20) -- Get local IN_SYNC value EXEC insync_local = get_insync_local -- Get server's IN_SYNC value EXEC insync_server = get_insync_server --SELECT IF (insync_local < insync_server) BEGIN 1 END ELSE 2 ------------------------------------------------------------ The procedures get_insync_* returns the right values, but executing this procedure returns the first get_insync_local value, and ...

  1. #1

    Default Procedure returns first EXEC not last?

    Hi,

    I want to create a procedure that checks whether a server is updated or not.
    I compare a number on the client with a number on the server - the one with
    the smallest number is not updated.

    Here is some code from my procedure:
    ------------------------------------------------------------
    DECLARE insync_local int, insync_server int, external_datasource
    varchar(20)

    -- Get local IN_SYNC value
    EXEC insync_local = get_insync_local

    -- Get server's IN_SYNC value
    EXEC insync_server = get_insync_server

    --SELECT IF (insync_local < insync_server) BEGIN 1 END ELSE 2
    ------------------------------------------------------------

    The procedures get_insync_* returns the right values, but executing this
    procedure returns the first get_insync_local value, and my last SELECT IF
    does not validate at all.

    What am I doing wrong?

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering


    Alexander Guest

  2. #2

    Default Re: Procedure returns first EXEC not last?

    "Alexander Ris°y" <no> wrote in message
    news:Q3iRa.6640$e.nsc.no... 
    not. 
    with 

    Uhm, looks like the SELECT statement is commented out?!

    Anyway, you must rewrite the statement slightly as the IF cannot be
    used "within" a SELECT.

    Either do:
    SELECT CASE WHEN insync_local < insync_server THEN 1 ELSE 2 END

    or

    IF insync_local < insync_server
    SELECT 1
    ELSE
    SELECT 2


    /johan


    johan Guest

  3. #3

    Default Re: Procedure returns first EXEC not last?

    Cheers for the IF rewrite (it was just commented out because it wouldn't
    validate).

    But the procedure still returns whatever value the first EXEC returns. Know
    how to solve this?

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering

    "johan olofsson" <se> skrev i melding
    news:phx.gbl... 
    > not. 
    > with [/ref]
    IF 
    >
    > Uhm, looks like the SELECT statement is commented out?!
    >
    > Anyway, you must rewrite the statement slightly as the IF cannot be
    > used "within" a SELECT.
    >
    > Either do:
    > SELECT CASE WHEN insync_local < insync_server THEN 1 ELSE 2 END
    >
    > or
    >
    > IF insync_local < insync_server
    > SELECT 1
    > ELSE
    > SELECT 2
    >
    >
    > /johan
    >
    >[/ref]


    Alexander Guest

  4. #4

    Default Re: Procedure returns first EXEC not last?

    > Maybe you can post the complete code to your procedure, so we have a
    chance 

    If you go four steps back in this thread you will find the code (even at the
    bottom of the posting your replied to), but I will repost for your
    convenience:

    Here is some code from my procedure:
    ------------------------------------------------------------
    DECLARE insync_local int, insync_server int, external_datasource
    varchar(20)

    -- Get local IN_SYNC value
    EXEC insync_local = get_insync_local

    -- Get server's IN_SYNC value
    EXEC insync_server = get_insync_server

    IF insync_local < insync_server
    SELECT 1
    ELSE
    SELECT 2
    ------------------------------------------------------------
    The bottom IF statement is slightly changed, but makes no difference to my
    problems.
     

    The purpose of the procedure, which I have called 'is_insync', is to return
    1 or 0 depending on whether the client and the server is synchronised or
    not. If there are better ways of doing this than using procedures, please
    inform me. I mean to make a procedure that synchronises the machines and
    just calles the above procedure to get the current status.

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering

    "Erland Sommarskog" <se> skrev i melding
    news:0.0.1... 
    >
    > Maybe you can post the complete code to your procedure, so we have a[/ref]
    chance 


    Alexander Guest

  5. #5

    Default Re: Procedure returns first EXEC not last?

    Alexander Ris°y (no) writes: 

    1) That was only an extract. Not the complete procedure.
    2) Since you posted the extract there were changes to it.

    I still don't understand what you mean with "procedure returns first EXEC
    not last". You say:
     

    But your snippet has:
     

    Maybe you should not only post the complete code to the outer procedure,
    but also the code to the inner procedures. If you prefer to post snippets
    only, that is your choice, but it may affectly negatively on your chances
    to get assistance with your problem.

    I would also like to point that the preferred way to returning a single
    value to the client is to use an OUTPUT parameter. Not a big deal for a
    single call, but if you are making many calls to the same procedure,
    there is a huge difference in performance if you get the value back as
    a result set or as an output parameter.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: Procedure returns first EXEC not last?

    Alexander, Erland,

    A stored procedure always returns ALL the ouput it generates to the calling
    ADO object. By default, your recordset shows you the first output. If you
    want to see more, use the NextRecordset method.
    In this case, however, why do you insist on using a stored procedure that
    returns a recordset with one column? Just create a user-defined function for
    that.

    Martin

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > 1) That was only an extract. Not the complete procedure.
    > 2) Since you posted the extract there were changes to it.
    >
    > I still don't understand what you mean with "procedure returns first EXEC
    > not last". You say:

    >
    > But your snippet has:

    >
    > Maybe you should not only post the complete code to the outer procedure,
    > but also the code to the inner procedures. If you prefer to post snippets
    > only, that is your choice, but it may affectly negatively on your chances
    > to get assistance with your problem.
    >
    > I would also like to point that the preferred way to returning a single
    > value to the client is to use an OUTPUT parameter. Not a big deal for a
    > single call, but if you are making many calls to the same procedure,
    > there is a huge difference in performance if you get the value back as
    > a result set or as an output parameter.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Martin Guest

  7. #7

    Default Re: Procedure returns first EXEC not last?

    Here are my procedures:

    -----------------------------------
    PROC get_insync_local
    -----------------------------------
    CREATE Procedure get_insync_local
    As
    SELECT Num AS insync FROM tblSysvars WHERE VarID = 'IN_SYNC'

    -----------------------------------
    PROC get_insync_server
    -----------------------------------
    CREATE Procedure get_insync_server
    As

    DECLARE external_datasource varchar(20)

    -- Get the name of the central SQL Server
    SELECT external_datasource = Val FROM tblSysvars WHERE VarID = 'SQL_MAIN'

    EXEC('SELECT Num AS insync FROM [' + external_datasource +
    '].OvnDB.dbo.tblSysvars WHERE VarID = ''IN_SYNC'' ')

    -----------------------------------
    PROC is_insync
    -----------------------------------
    CREATE Procedure is_insync
    As

    DECLARE insync_local int, insync_server int

    -- Get local IN_SYNC value
    EXEC insync_local = get_insync_local

    -- Get server's IN_SYNC value
    EXEC insync_server = get_insync_server

    -- Check if machines are in sync (corrected)
    IF insync_local <> insync_server
    SELECT 0
    ELSE
    SELECT 1

    -----------------------------------
    Running it in OSQL produces:
    -----------------------------------
    1> exec is_insync
    2> go
    exec is_insync
    insync
    -----------
    6

    (1 row affected)
    insync
    -----------
    8

    (1 row affected)

    -----------
    1

    (1 row affected)
    -----------------------------------

    .... and thus the IF seem to return 1 no matter what the values that are
    compared are. The values are probably not stored in the local variables I
    have set.
     

    By this I mean that if I run the above procedure in Access it returns the
    value of the first execute statement. In the above example that would be 6.
    I want the result of the last IF statement to be returned.

    Eventually I would want to check if the machines are "in sync" and update
    the one that isn't.

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering

    "Erland Sommarskog" <se> skrev i melding
    news:0.0.1... 
    >
    > 1) That was only an extract. Not the complete procedure.
    > 2) Since you posted the extract there were changes to it.
    >
    > I still don't understand what you mean with "procedure returns first EXEC
    > not last". You say:

    >
    > But your snippet has:

    >
    > Maybe you should not only post the complete code to the outer procedure,
    > but also the code to the inner procedures. If you prefer to post snippets
    > only, that is your choice, but it may affectly negatively on your chances
    > to get assistance with your problem.
    >
    > I would also like to point that the preferred way to returning a single
    > value to the client is to use an OUTPUT parameter. Not a big deal for a
    > single call, but if you are making many calls to the same procedure,
    > there is a huge difference in performance if you get the value back as
    > a result set or as an output parameter.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Alexander Guest

  8. #8

    Default Re: Procedure returns first EXEC not last?

    Alexander, Erland,

    A stored procedure always returns ALL the ouput it generates to the calling
    ADO object. By default, your recordset shows you the first output. If you
    want to see more, use the NextRecordset method.
    In this case, however, why do you insist on using a stored procedure that
    returns a recordset with one column? Just create a user-defined function for
    that.

    Martin

    "Alexander Ris°y" <no> wrote in message
    news:Q3iRa.6640$e.nsc.no... 
    not. 
    with 


    Martin Guest

  9. #9

    Default Re: Procedure returns first EXEC not last?

    Alexander Ris°y (no) writes: 

    This procedure produces a result set. The retun value from this function
    is always 0.
     

    Ditto.
     

    IF 0 <> 0 SELECT 0 ELSE SELECT 1

    It's not that I want to be rude, but you appear to lack an understanding
    of how stored procedure works.

    A stored procedure can return data in three different ways:

    1) As a result set with a SELECT statement. This data normaly goes to the
    client, but you can catch it from T-SQL with INSERT-EXEC.
    2) An OUTPUT parameter. The output parameter can be retrieved from a
    calling T-SQL procedure, or from client code.
    3) A return value, with the RETURN statement.

    Here is a sample procedure which utilizes all these methods:

    CREATE PROCEDURE alexander x int OUTPUT AS
    DECLARE y int
    SELECT x = COUNT(*) FROM sysobjects
    SELECT minid = MIN(id) FROM sysobjects
    SELECT y = MAX(id) FROM sysobjects
    go
    DECLARE ret int, z int
    EXEC ret = alexander z OUTPUT
    SELECT maxid = ret, cnt = z

    The output is:

    minid
    -----------
    1

    (1 row(s) affected)

    maxid cnt
    ----------- -----------
    0 22

    (1 row(s) affected)


    Note: here I use the RETURN statement to return data. As I've said
    previously, in real programming, The RETURN statement should only
    be used to return success/failure information. Use OUTPUT parameters
    for data.

    I hope the example here, will make it possible for you to rewrite your
    procedures.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  10. #10

    Default Re: Procedure returns first EXEC not last?

    Martin Lingl (at) writes: 

    The number of columns have nothing to do with it. But since it is a one-row
    result set, an OUTPUT parameter would be better.

    UDF? You cannot call stored procedures from a UDF, and you cannot invoke
    dynamic SQL which Alexander is using.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Example of using a stored procedure that returns a cursor
    By George Viveiros in forum ASP Database
    Replies: 2
    Last Post: January 13th, 09:09 PM
  2. Replies: 1
    Last Post: November 5th, 08:20 AM
  3. Replies: 0
    Last Post: November 5th, 08:13 AM
  4. Exec procedure and date parameters
    By Emmanuel in forum Microsoft SQL / MS SQL Server
    Replies: 12
    Last Post: July 16th, 10:51 PM
  5. Replies: 0
    Last Post: December 10th, 09:27 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139