Professional Web Applications Themes

Stored Procedure not returning values. - ASP

There are two problems: 1. SQL Server returns the "x number of records affected" message that you see in Query yzer to the client as a resultset. To prevent this, use "set nocount on" at the beginning of your procedure as shown below. 2. You do not use Return to cause the procedure to return the results of a select statement. "Return" is used to return a single numeric value (usually an error number) to the client (which is ADO when using asp to run the procedure), which can only read the value via an ADO Command object. In your ...

  1. #1

    Default Re: Stored Procedure not returning values.

    There are two problems:

    1. SQL Server returns the "x number of records affected" message that you
    see in Query yzer to the client as a resultset. To prevent this, use
    "set nocount on" at the beginning of your procedure as shown below.

    2. You do not use Return to cause the procedure to return the results of a
    select statement. "Return" is used to return a single numeric value (usually
    an error number) to the client (which is ADO when using asp to run the
    procedure), which can only read the value via an ADO Command object. In your
    case, you should be able to solve your problem by removing the "Return"
    keyword.

    HTH,
    Bob Barrows

    PS. For more info on running stored procedures from ADO, see this message:
    [url]http://tinyurl.com/jyy0[/url]

    Giri wrote:
    > I am trying to run this stored procedure, which i am not able to
    > return any values. If i run the select statement all by itself i can
    > see the two rows output. Here's the stored procedure .... pleas help.
    >
    > CREATE proc shipqc
    > bol nvarchar(50)
    > as
    set nocount on
    >
    > create table #temp1
    > (
    > so nvarchar (50) null,
    > qty int null,
    > qtyship int null,
    > bol nvarchar (50) null,
    > item numeric (9) null,
    > partnum nvarchar (50) null,
    > rev nvarchar (50) null,
    > status nvarchar (50) null
    > )
    >
    > insert into #temp1 select
    > so,qty,qtyship,bol_no,so_item_no,partnum,rev,statu s from efsdetail
    > where bol_no='poi' and groupcode='LM' and status='kitted'
    >
    >
    > return select distinct * from #temp1 order by bol,item
    > GO


    Bob Barrows Guest

  2. #2

    Default Re: Stored Procedure not returning values.

    PS. Why are you using a #temp table? And what is the purpose of the bol
    parameter?

    This procedure will do the same as yours, but much more efficiently:

    CREATE proc shipqc
    as
    set nocount on
    select
    so,qty,qtyship,bol_no,so_item_no,partnum,rev,statu s
    from efsdetail
    where bol_no='poi' and groupcode='LM' and status='kitted'
    order by bol,item


    Giri wrote:
    > I am trying to run this stored procedure, which i am not able to
    > return any values. If i run the select statement all by itself i can
    > see the two rows output. Here's the stored procedure .... pleas help.
    >
    > CREATE proc shipqc
    > bol nvarchar(50)
    > as
    >
    > create table #temp1
    > (
    > so nvarchar (50) null,
    > qty int null,
    > qtyship int null,
    > bol nvarchar (50) null,
    > item numeric (9) null,
    > partnum nvarchar (50) null,
    > rev nvarchar (50) null,
    > status nvarchar (50) null
    > )
    >
    > insert into #temp1 select
    > so,qty,qtyship,bol_no,so_item_no,partnum,rev,statu s from efsdetail
    > where bol_no='poi' and groupcode='LM' and status='kitted'
    >
    >
    > return select distinct * from #temp1 order by bol,item
    > GO


    Bob Barrows Guest

  3. #3

    Default Re: Stored Procedure not returning values.

    So I di...I mean: no, I didn't - I left that as an exercise for the user ...
    ;-)

    Tom B wrote:
    > I think you forgot the DISTINCT
    >
    > "Bob Barrows" <reb_01501> wrote in message
    > news:%23sFLgehYDHA.1816TK2MSFTNGP09.phx.gbl...
    >> PS. Why are you using a #temp table? And what is the purpose of the
    >> bol parameter?
    >>
    >> This procedure will do the same as yours, but much more efficiently:
    >>
    >> CREATE proc shipqc
    >> as
    >> set nocount on
    >> select
    >> so,qty,qtyship,bol_no,so_item_no,partnum,rev,statu s
    >> from efsdetail
    >> where bol_no='poi' and groupcode='LM' and status='kitted'
    >> order by bol,item
    >>
    >>
    >> Giri wrote:
    >>> I am trying to run this stored procedure, which i am not able to
    >>> return any values. If i run the select statement all by itself i can
    >>> see the two rows output. Here's the stored procedure .... pleas
    >>> help.
    >>>
    >>> CREATE proc shipqc
    >>> bol nvarchar(50)
    >>> as
    >>>
    >>> create table #temp1
    >>> (
    >>> so nvarchar (50) null,
    >>> qty int null,
    >>> qtyship int null,
    >>> bol nvarchar (50) null,
    >>> item numeric (9) null,
    >>> partnum nvarchar (50) null,
    >>> rev nvarchar (50) null,
    >>> status nvarchar (50) null
    >>> )
    >>>
    >>> insert into #temp1 select
    >>> so,qty,qtyship,bol_no,so_item_no,partnum,rev,statu s from efsdetail
    >>> where bol_no='poi' and groupcode='LM' and status='kitted'
    >>>
    >>>
    >>> return select distinct * from #temp1 order by bol,item
    >>> GO

    Bob Barrows Guest

Similar Threads

  1. Stored procedure returning only one byte in outputparameter
    By baitband in forum Coldfusion Database Access
    Replies: 12
    Last Post: August 12th, 12:03 AM
  2. Help - stored procedure not returning a recordset
    By Bill S. in forum ASP Database
    Replies: 5
    Last Post: February 13th, 10:06 AM
  3. Stored Procedure Not Returning
    By Amanda Osment in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:07 PM
  4. Stored Procedure not returning the wanted resultset
    By Henning Kongsgaard in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 08:01 AM
  5. Help with Stored Procedure returning recordset
    By Ivar in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 04:31 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