Solved

get return value of storeprocedure in SQL Query Analyzer

Posted on 2004-04-13
18
672 Views
Last Modified: 2012-06-21
I have a storeprocedure calledsp_ins_order. It will return a value. how can i get the value and display in the SQL Query Analyzer ?

for example.

if i run sp_ins_order in the SQL Query Analyzer, it show the command completed successfully.

But i want to see the value that the storeprocedure return. would someone pls help me? Thanks
0
Comment
Question by:mikekwok
  • 8
  • 3
  • 3
  • +2
18 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10819579
Post the proc....DO you mean the proc returns and output parameter?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10819612
I'm off to bed--if you are talking about output parameters.

Take a look in books online--search for output parameters and then choose "returning data using output parameters"
0
 

Author Comment

by:mikekwok
ID: 10819621
In PowerBuilder, the code is as follow :
retval = SQLCA.sp_ins_trnaeorder(refodrno, g_userid, odrtype, stock, price, redqty, client, turbo, plctype)

I would like to ask how to get the return value of the storeprocedure in SQL Query Analzyer?
0
 
LVL 1

Expert Comment

by:gcdba
ID: 10819756
Very simple...

Within the stored proc use a print statement to print the variable

Print @variablename


By the way... for performance reasons you should drop the sp prefix from a stored procedure. SQL Server looks for the sp prefix and does not index. Therefore you lose performance. To test... run a profiler on regular stored proc and one with prefix.
0
 

Author Comment

by:mikekwok
ID: 10819773
after I have added print @retval in the stored proc.
in SQL Query Analyzer ,i try to run the store proc, i still cannot get the return value. It just said the command completed successfully.
would u pleas help me? Thanks.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10819796
Like I said above--post the actual proc--it's probably using an output parm--you can't simply print it--you have to declare the output variable when you call it...
0
 

Author Comment

by:mikekwok
ID: 10819817
REATE PROCEDURE [sp_ins_trnaeorder]                         
      @refodrno      int,
      @odrowner      varchar(20),
      @odrtype            varchar(5),
      @stkcode            varchar(10),
      @odrprice      decimal(18, 5),
      @odrqty            decimal(18, 5),
      @cltcode            varchar(10),
      @turbo            tinyint,
      @plctype            char,
      @pricecfm      char,
      @sqtycfm            char,
      @suspcfm      char,
      @aecrcfm            char,
      @maxlotcfm      char,
      @chgflag            varchar(2),
      @defdealer      varchar(5),                        
      @OdrPriceStr      varchar(10),
      @OdrGrpNo      int,
      @OdrGrpFlag      char,
      @DisMtd            char,
      @OddLot            char,                              -- [Y] / [N]                                     2000-12-08
      @BSOdrType      char,                              -- [L]imited / [E]nhanced / [S]pecial                         2001-02-18
      @CnfmFlag      char,                               -- [B], [C], [N]            -- Price Warning ConfirmFlag        2001-03-02
      @LastPrice      decimal(18, 5)       OUTPUT,                  -- Price Warning,                                     2001-03-02
      @OutSpread      int            OUTPUT,                  -- Price Warning,                                     2001-03-02            
      @ExtRef            varchar(10),
      @ShortSell      char,
      @OdrSeqNo      varchar(10),
      @Parm1            varchar(10),                        -- OddLot Function -> Parm1[1] = [O]rigin, Parm1[2] = [H]edge, Parm1[3-6] = ToBroker
      @Parm2            varchar(20),                        -- WorkParm -> Parm2[1] = [O]ffice, Parm2[1] = [I]nternet
      @comment            varchar(20)                        -- 2
AS
      SET NOCOUNT ON
declare       @odrno             int
declare       @status            varchar(5)
declare      @dealerid            varchar(5)            
declare      @lnkodrno      int
declare      @retcode            int
declare       @errcode       int
declare      @errprc            varchar(30)
declare      @errmsge            varchar(100)
declare      @logonstatus      char

      -- Initialise variables
      SELECT @errcode = 1, @lnkodrno = 0, @errprc = 'sp_ins_trnaeorder', @errmsge = 'Initial Err Msge'
      
      BEGIN TRANSACTION

      -- Input Checkings
      IF @odrtype NOT IN ('B-N', 'S-N', 'B-C', 'S-C')                                          -- BuyNew, SellNew, BuyChange, SellChange
      BEGIN
            SELECT @errmsge = 'OdrType NOT IN  (B-N, S-N, B-C, S-C)'
            GOTO ERR_END
      END
      IF @plctype NOT IN ('I', 'N', 'R')                                                -- (I)nvisible, (N)ormal, BackLog (R)
      BEGIN
            SELECT @errmsge = 'PlcType NOT IN (I, N, R)'
            GOTO ERR_END
      END
      IF @chgflag NOT IN ( 'R', 'C', 'P', 'N', 'M', 'TL')                                          -- (R)educeQty, (C)ancel, Change(P)rice, (N)ewOrder, (M)odifyOrder
      BEGIN                                                                  -- (TL)TradeOddLot
            SELECT @errmsge = 'ChgFlag NOT IN (R, C, P, N, M, TL)'
            GOTO ERR_END
      END
      IF @odrprice < 0 or (@odrqty = 0 AND @chgflag <> 'M')                                    --  2001-03-05
      BEGIN
            SELECT @errmsge = 'OdrPrice < 0 or (OdrQty = 0 and ChgFlag <> M)'
            GOTO ERR_END
      END
      IF @ShortSell = 'Y' AND LEFT(@OdrType, 1) <> 'S'
      BEGIN
            SELECT @errmsge = 'Short Sell OdrType NOT IN  (S-N, S-C)'
            GOTO ERR_END
      END
      IF @ChgFlag = 'TL' AND (@OdrSeqNo = '-' OR @OdrSeqNo = '' OR @OdrSeqNo IS NULL)             --  2001-08-20 (TradeOdd Lot)
      BEGIN
            SELECT @errmsge = 'Odd Lot Trade, Invalid OdrSeqNo'
            GOTO ERR_END
      END
      
      -- Variable Assignments
      IF @DefDealer IS NULL OR LEN(@DefDealer) = 0 OR @DefDealer = ''      SELECT @DefDealer = '-'            --  2001-03-12
      SELECT @dealerid       = @DefDealer
      EXEC sp_cvt_stode @Stode OUTPUT      
      IF @BSOdrType NOT IN ('L', 'E', 'S', 'A', 'I')                        SELECT @BSOdrType = 'L'            -- [L]imited / [E]nhanced / [S]pecial Order Type /[A]ution /L[I]mited Aution
      IF @Turbo <= 0 OR @Turbo >=10                              SELECT @Turbo = 6
      IF @OddLot NOT IN ('Y', 'N')                                    SELECT @OddLot = 'N'
      IF @CnfmFlag  NOT IN ('B', 'N', 'A')                              SELECT @CnfmFlag = 'B'            -- Price Warning Confirm Flag
      IF @OdrPrice > 0                                           SELECT @OdrPriceStr = CAST (CAST(@OdrPrice as decimal(18, 3)) as varchar(20))

-- 1
--      IF @ShortSell NOT IN ('Y', 'N')                              SELECT @ShortSell = 'N'
--      IF @ShortSell NOT IN ('Y', 'N', 'M')                         SELECT @ShortSell = 'N'
      IF @ShortSell = ' ' or @ShortSell = '' or @ShortSell is null or len(@ShortSell) <= 0       SELECT @ShortSell = 'N'

      IF @ChgFlag <> 'TL'                                    SELECT @OdrSeqNo = '-'            --  2001-08-20

      -- SamsonLai: 2001-12-07 11:48      ***** Hui Kai Only *****
      IF Upper(@OdrOwner) IN ('HOUSEMAN', 'KEUNG', 'SEREENA', 'OSCAR', 'PAUL')      SELECT @Turbo = 3
      -- End: SamsonLai 2001-12-07

      -- INSERT A REDUCE-QTY ORDER OR CANCEL ORDER (B-C, S-C) (chgflag = R, C, P, M)
      IF (@odrtype = 'B-C'  and @chgflag <> 'N') OR (@odrtype = 'S-C' and @chgflag <> 'N')
      BEGIN
            EXEC @retcode = sp_ins_chgtrnaeorder @refodrno, @odrowner, @odrtype, @stode, @odrprice, @odrqty, @cltcode, @turbo, @plctype,
                        @chgflag, @errcode OUTPUT, @errprc OUTPUT, @errmsge OUTPUT, @odrno OUTPUT, @status OUTPUT, @dealerid OUTPUT,
                        @OdrPriceStr, @OdrGrpNo, @OdrGrpFlag, @DisMtd, @CnfmFLag, @ExtRef, @Parm1, @Parm2, @SQtyCfm OUTPUT

            IF @retcode <> 0
            BEGIN
                  IF @retcode > 900       GOTO ERR_CHECK

                  GOTO ERR_END
            END

            -- CONVERT DATA FOR CHANGE-PRICE ORDER (P)
            IF @chgflag  IN ( 'P', 'M')
            BEGIN
                  -- INSERT LOG_EVENT FOR NEW CHANGE ORDER
                  EXEC @retcode = sp_ins_logevent @odrowner, @odrno
                  IF @retcode <> 0
                  BEGIN
                        SELECT @errmsge = 'OdrNo: ' + cast(@odrno  as varchar(10)) + ', Error inserting record into Log_Event.'
                        GOTO ERR_END
                  END
      
                  -- Insert log_event (original order no.) if  @status = 'PL' and @dealerid = 'AUTO'
                  IF @dealerid = 'AUTO' AND @status = 'PL'
                  BEGIN
                        EXEC @retcode = sp_ins_logevent @odrowner, @refodrno
                        IF @retcode <> 0
                        BEGIN
                              SELECT @errmsge = 'RefOdrNo: ' + cast(@refodrno  as varchar(10)) + ', Error inserting record into Log_Event.'
                              GOTO ERR_END
                        END
                  END
            
                  IF @odrtype = 'B-C'            SELECT @odrtype = 'B-N'
                  IF @odrtype = 'S-C'            SELECT @odrtype = 'S-N'

                  SELECT @lnkodrno = @odrno
                  IF @chgflag = 'P'                        SELECT @odrqty =  - @odrqty
                  IF @dealerid = 'AUTO' AND @status = 'PL'      SELECT @dealerid = @defdealer, @chgflag = 'N', @lnkodrno = 0            -- Modified By Samson: 2000-11-06
                  IF @dealerid = 'AUTO' AND @status = 'RJ'      GOTO PRC_END
            END
      END

      -- INSERT A NEW ORDER (B-N, S-N)
      IF @odrtype IN ('B-N', 'S-N')
      BEGIN
            EXEC @retcode = sp_ins_newtrnaeorder  @odrowner, @odrtype, @stode, @odrprice, @odrqty, @cltcode, @turbo, @plctype,
                        @pricecfm, @sqtycfm, @suspcfm, @aecrcfm, @maxlotcfm, @chgflag, @lnkodrno,
                        @errcode OUTPUT, @errprc OUTPUT, @errmsge OUTPUT, @odrno OUTPUT, @status OUTPUT, @dealerid OUTPUT,
                        @LastPrice OUTPUT, @OutSpread OUTPUT, @OdrPriceStr, @OdrGrpNo, @OdrGrpFlag, @DisMtd, @OddLot, @BSOdrType,
                        @CnfmFlag, @ExtRef, @ShortSell, @OdrSeqNo, @Parm1, @Parm2, @comment   --2

            IF @retcode <> 0
            BEGIN
                  IF @retcode > 900       GOTO ERR_CHECK

                  GOTO ERR_END
            END
      END

      -- INSERT LOG_EVENT FOR NEW ORDER
      EXEC @retcode = sp_ins_logevent @odrowner, @odrno
      IF @retcode <> 0
      BEGIN
            SELECT @errmsge = 'OdrNo: ' + cast(@odrno  as varchar(10)) + ', Error inserting record into Log_Event.'
            GOTO ERR_END
      END
      
      -- Insert log_event (original order no.) if @status = 'PL' and @dealerid = 'AUTO'
      IF @status = 'PL' and @dealerid = 'AUTO'
      BEGIN
            EXEC @retcode = sp_ins_logevent @odrowner, @refodrno
            IF @retcode <> 0
            BEGIN
                  SELECT @errmsge = 'RefOdrNo: ' + cast(@refodrno  as varchar(10)) + ', Error inserting record into Log_Event.'
                  GOTO ERR_END
            END

-- ***********************************************  2001-03-17 * Important, Don't Modified ******************************************************

            EXEC @retcode = sp_og_sendmsge @refodrno
-- ************************************************************************************************************************************************
      END


PRC_END:
      COMMIT TRANSACTION

      RETURN (0)


ERR_END:
      ROLLBACK TRANSACTION      

      EXEC sp_ins_logerror @errcode, @odrowner, @errprc, @errmsge
      RETURN (1)


ERR_CHECK:
      ROLLBACK TRANSACTION

      RETURN (@retcode)

GO
0
 

Author Comment

by:mikekwok
ID: 10819821
IT is the stored procedure . would you please help me ?
0
 

Author Comment

by:mikekwok
ID: 10819826
I will increase the point to 70.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10819915
>>I will increase the point to 70.<<

Too many open questions.  For the record:
1 11/11/2003 50 jComboBox + Press Enter  Open Java Programming
2 02/17/2004 50 String Generation  Open Visual Basic
3 03/02/2004 50 Read HTML Table data and calculation  Open HTML
4 03/02/2004 20 Read HTMl Table and calcuation  Open Visual Basic
5 03/11/2004 20 max length of string  Open Active Server Pages (ASP)
0
 
LVL 13

Accepted Solution

by:
danblake earned 70 total points
ID: 10820927
A quick demo...
declare @retval int
exec @retval = SQLCA.sp_ins_trnaeorder(refodrno, g_userid, odrtype, stock, price, redqty, client, turbo, plctype)
print @retval

The return value for the Sproc can only be returned within the executing shell (otherwise you will get the state in time value within the sp -- not what you want ?).

Example from northwind..

declare @retcode int
exec @retcode = custOrderHist 5
print @retcode

This executes sucesfully and returns 0
0
 

Author Comment

by:mikekwok
ID: 10821085
I also get a tutorial on that here : http://www.4guysfromrolla.com/webtech/010600-1.shtml
0
 
LVL 13

Expert Comment

by:danblake
ID: 10821101
@Mikekwok, and the relevance ?  ADO code cannot currently be run in QA ?

The demo code I have given can be run in QA as it is T-SQL.
0
 

Author Comment

by:mikekwok
ID: 10821171
Ok, you are right, thanks for your code i will test it tonite, thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10829393
Let me know if you need help maintaining your old open questions.
0
 

Author Comment

by:mikekwok
ID: 10829782
acperkins , what do u mean "maintaining your old open questions" ?
do u mean u will help me to solve the problems of my old posts?
some of my old post is still opening because some of them are just links forward to the other questions.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10832488
>>what do u mean "maintaining your old open questions" ?<<
I mean like give you suggestions as to how you should close them.

>>some of my old post is still opening because some of them are just links forward to the other questions.<<
If they are over 21 days they are technically abandoned and should be closed.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10833783
How are you executing the stored procedure ?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
(To return your OUTPUT results..)

It does depend on how you / what exactly you are trying to capture.

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now