Professional Web Applications Themes

Stored Procedure not returning the wanted resultset - Microsoft SQL / MS SQL Server

My stored procedure is not returning the resultset I am expecting. The procedure is: CREATE PROCEDURE hms.getHMStest( userid int, userrole varchar(15) = NULL, owner_id int = 0, Tot int output, ant_er_rev int output ) AS begin declare Ret_tot int declare bisnull int declare thms_id varchar(20) declare tlocal_id int declare thasRevisions bit declare ut int declare tmp_ant int set nocount on --create temporary table to hold the id of the doents CREATE TABLE #tmp_hms1( hms_id varchar(20), local_id int, hasRevisions bit ) declare c1 cursor read_only for --Get all doents for the user select distinct h.hms_id, h.local_id FROM produkt_prodgruppe p RIGHT OUTER ...

  1. #1

    Default Stored Procedure not returning the wanted resultset

    My stored procedure is not returning the resultset I am expecting. The
    procedure is:

    CREATE PROCEDURE hms.getHMStest(
    userid int,
    userrole varchar(15) = NULL,
    owner_id int = 0,
    Tot int output,
    ant_er_rev int output
    )

    AS

    begin

    declare Ret_tot int
    declare bisnull int

    declare thms_id varchar(20)
    declare tlocal_id int
    declare thasRevisions bit
    declare ut int
    declare tmp_ant int

    set nocount on

    --create temporary table to hold the id of the doents
    CREATE TABLE #tmp_hms1(
    hms_id varchar(20),
    local_id int,
    hasRevisions bit
    )

    declare c1 cursor read_only for
    --Get all doents for the user
    select distinct h.hms_id, h.local_id
    FROM produkt_prodgruppe p RIGHT OUTER JOIN
    hms1 h INNER JOIN
    owner o ON h.owner_id = o.owner_id INNER JOIN
    users u ON o.owner_id = u.owner_id ON
    p.uproduct_id = h.product_id
    WHERE o.owner_id = owner_id
    ORDER BY h.local_id

    open c1

    --Actuall number of rows returned (doents)
    select tmp_ant = CURSOR_ROWS

    fetch next from c1 into thms_id, tlocal_id

    while fetch_status = 0
    begin

    --Check if the doent has a newer revision and how many
    exec stp_getNewerRevisions thms_id, ut out
    --If the doents has a revision. If ut = 0, it doesn't have any.
    thasRevisions is just a flag
    if ut = 0
    select thasRevisions = 0
    else
    select thasRevisions = 1

    insert into #tmp_hms1 values(thms_id, tlocal_id, thasRevisions)

    fetch next from c1 into thms_id, tlocal_id
    end

    close c1
    deallocate c1

    --Return the actual number of hits
    select Tot = ROWCOUNT
    --return the number of revisions
    select ant_er_rev = tmp_ant - Tot

    --This is what I want the stored procedure to return. I get the data
    throuhg an ADO Command object and a Recordset object
    select * from hms1 h inner join #tmp_hms1 t on t.hms_id = h.hms_id
    where t.hasRevisions = 0

    drop table #tmp_hms1

    end
    GO

    Inside my stored procedure, I call an other SP. This looks like this:

    CREATE PROCEDURE hms.stp_getNewerRevisions(hms_id varchar(20), ant int
    out) AS

    begin

    select p1.*
    from hms_tree p1, hms_tree p2
    where p1.lft between p2.lft and p2.rgt
    and p2.hms_id = hms_id
    and p1.hms_id <> hms_id
    order by p1.lft desc

    select ant = rowcount

    end
    GO

    If I run this from QA, I see that this last SP returns a resultset for
    each time it is called within the cursor.

    Therefore, the resultset that I want to return to my asp page:
    select * from hms1 h inner join #tmp_hms1 t on t.hms_id = h.hms_id
    where t.hasRevisions = 0

    is never returned, because somehow the SP returns an empty resultset
    from the execution of the other procedure.

    Any good tips?

    Thanks a lot

    Henning

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Henning Kongsgaard Guest

  2. #2

    Default Re: Stored Procedure not returning the wanted resultset

    Thanks Mr. Celco!
    I will provide you with the DDL of the tables in question.
    To start with, I will explain what I am trying to do. The table hms1 is
    a table that houses information about a doent. hms_id is the PK. Now.
    It is essential that the system provides functionality that allows the
    user to be able to create new revisions of the doents. The number of
    revisions is X. To be able to deal with this, I read your article here:
    [url]http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.htm[/url]
    l

    It suggested I'd build a tree that handles the relation between, in my
    case, my doents.

    Therefore, I have made a table called hms_tree. I contains the id of the
    doent and lft and rgt. The top node has an ID of 'root', and lft = 1
    and rgt = n. All first revisions of a doent will have a depth of 2.
    After that, revisions are added as child nodes.

    When presenting the doents to the user, I have to present the latest,
    and only the latest revision of a doent. In addition the user should
    be able to follow a link to previous revisions. I was unfortunatly not
    able to make a query that would do this for me in one resultset that
    could be used in my asp page. Therefore all the "workarounds".

    I appretiate all the help I can get. I am not an DBA as you probably can
    tell.

    Here are the tables:

    CREATE TABLE [hms].[hms1] (
    [owner_id] [int] NOT NULL ,
    [hms_id] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
    [local_id] [int] NULL ,
    [trade_name] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [product_id] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NULL ,
    [pr_nr] [varchar] (15) COLLATE Danish_Norwegian_CI_AS NULL ,
    [manufacturer] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [street_adr] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [postal_adr] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [postal_code] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NULL ,
    [city] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [country] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NULL ,
    [phone] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NULL ,
    [fax] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NULL ,
    [email] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [www] [varchar] (100) COLLATE Danish_Norwegian_CI_AS NULL ,
    [contact] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [doc_author] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
    [emergency_phone1] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [emergency_phone2] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
    [status] [char] (2) COLLATE Danish_Norwegian_CI_AS NULL ,
    [revision_date] [datetime] NULL ,
    [last_saved] [datetime] NULL ,
    [url_hms] [varchar] (100) COLLATE Danish_Norwegian_CI_AS NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [hms].[hms1] WITH NOCHECK ADD
    CONSTRAINT [PK_hms1] PRIMARY KEY CLUSTERED
    (
    [hms_id]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [hms].[hms1] ADD
    CONSTRAINT [FK_hms1_owner] FOREIGN KEY
    (
    [owner_id]
    ) REFERENCES [hms].[owner] (
    [owner_id]
    ),
    CONSTRAINT [FK_hms1_produkt_prodgruppe] FOREIGN KEY
    (
    [product_id]
    ) REFERENCES [hms].[produkt_prodgruppe] (
    [uproduct_id]
    )
    GO

    CREATE TABLE [hms].[hms_tree] (
    [hms_id] [varchar] (20) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
    [lft] [int] NOT NULL ,
    [rgt] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [hms].[hms_tree] WITH NOCHECK ADD
    CONSTRAINT [PK_hms_tree] PRIMARY KEY CLUSTERED
    (
    [hms_id]
    ) ON [PRIMARY] ,
    CONSTRAINT [sibling_order_okay] CHECK ([lft] < [rgt])
    GO

    ALTER TABLE [hms].[hms_tree] ADD
    CONSTRAINT [FK_hms_tree_hms1] FOREIGN KEY
    (
    [hms_id]
    ) REFERENCES [hms].[hms1] (
    [hms_id]
    ) NOT FOR TION
    GO

    alter table [hms].[hms_tree] nocheck constraint [FK_hms_tree_hms1]
    GO

    Thanks a lot, I appretiate it!!!

    Henning :-)

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Henning Kongsgaard Guest

  3. #3

    Default Re: Stored Procedure not returning the wanted resultset

    Hi again!

    I hope you have the time to answer me. A haven't come any closer to a
    solution and I am in a hurry. If not, I will have to make some stupid
    solution to the problem, but it wouldn't be the first time :-)

    Thanks

    Henning

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Henning Kongsgaard Guest

Similar Threads

  1. Caching a resultset from a stored procedure
    By zax_srb in forum Coldfusion Database Access
    Replies: 0
    Last Post: January 26th, 03:15 PM
  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 values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12:31 PM
  4. Stored Procedure Not Returning
    By Amanda Osment in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:07 PM
  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