Professional Web Applications Themes

sproc output - Microsoft SQL / MS SQL Server

I need to perform some SQL based upon the output of another system sproc. So if the sproc returns 1 row, then i need to do something, if not then dont do anything.. How can i write a TSQL for that such as exec sprocA if ( # of rows returned is 1 ) then print ' Go ahead ' else print ' No records returned ' P.S. I cannot modify sprocA...

  1. #1

    Default sproc output

    I need to perform some SQL based upon the output of another system sproc.

    So if the sproc returns 1 row, then i need to do something, if not then dont
    do anything..

    How can i write a TSQL for that such as

    exec sprocA
    if ( # of rows returned is 1 ) then print ' Go ahead '
    else print ' No records returned '

    P.S. I cannot modify sprocA


    Hassan Guest

  2. #2

    Default Re: sproc output

    You might try:

    EXEC sprocA

    IF ROWCOUNT = 1
    PRINT ' Go ahead '
    ELSE
    PRINT ' No records returned '

    Note that this will only work if the last statement in the proc is the
    SELECT because subsequent statements in the proc may change the
    ROWCOUNT value.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Hassan" <com> wrote in message
    news:phx.gbl... 
    sproc. 
    then dont 


    Dan Guest

  3. #3

    Default Re: sproc output

    Hi Hassan,

    You can insert the results of the stored procedure in a temporary table and
    check the rowcount of that temporary table:

    CREATE TABLE #t (<column list of stored procedure result set>)
    INSERT INTO #t EXEC sp_get_composite_job_info job_id = 'XYZ',
    execution_status = 1
    IF EXISTS(SELECT NULL FROM #t)
    PRINT ' Go ahead '
    ELSE
    PRINT ' No records returned '
    DROP TABLE #t

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Hassan" <com> wrote in message
    news:#phx.gbl... 
    > > sproc. 
    > > then dont 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. SPROC Question, what am I doing wrong?
    By dj shane in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 25th, 10:31 PM
  2. SPROC vs Inline Times.
    By dj shane in forum Coldfusion Database Access
    Replies: 6
    Last Post: September 22nd, 05:50 PM
  3. sproc in DW asp.net
    By Laura K in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 12th, 09:10 PM
  4. SPROC parameters
    By RobGT in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 24th, 03:38 PM

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