Professional Web Applications Themes

Dropping a recordset during a stored procedure - Microsoft SQL / MS SQL Server

Hello, My question is that when my first recordset in my stored procedure is SELECTed, if rowcount is zero, I want to not return this recordset but instead return just SELECT 0. I tried the following but it did not work. Help would be greatly appreciated. Thanks. -- first get the default inspector for this property BEGIN TRAN SELECT dbo.tblRegion.Insptor_ID FROM dbo.tblRegion INNER JOIN dbo.tblProperty ON dbo.tblRegion.Reg_ID = dbo.tblProperty.Prop_Region WHERE dbo.tblProperty.Prop_ID = propid IF rowcount > 0 COMMIT TRAN ELSE BEGIN ROLLBACK TRAN SELECT 0 END Mia J. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded ...

  1. #1

    Default Dropping a recordset during a stored procedure

    Hello,

    My question is that when my first recordset in my stored procedure is
    SELECTed, if rowcount is zero, I want to not return this recordset but
    instead return just SELECT 0. I tried the following but it did not
    work. Help would be greatly appreciated. Thanks.


    -- first get the default inspector for this property
    BEGIN TRAN
    SELECT dbo.tblRegion.Insptor_ID FROM dbo.tblRegion INNER JOIN
    dbo.tblProperty ON dbo.tblRegion.Reg_ID = dbo.tblProperty.Prop_Region
    WHERE dbo.tblProperty.Prop_ID = propid

    IF rowcount > 0
    COMMIT TRAN
    ELSE
    BEGIN
    ROLLBACK TRAN
    SELECT 0
    END

    Mia J.

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

  2. #2

    Default Dropping a recordset during a stored procedure

    here is a work around(not efficient)

    if exists(SELECT 1 FROM dbo.tblRegion INNER JOIN
    dbo.tblProperty ON dbo.tblRegion.Reg_ID =
    dbo.tblProperty.Prop_Region
    WHERE dbo.tblProperty.Prop_ID = propid)
    SELECT dbo.tblRegion.Insptor_ID FROM
    dbo.tblRegion INNER JOIN dbo.tblProperty ON
    dbo.tblRegion.Reg_ID = dbo.tblProperty.Prop_Region
    WHERE dbo.tblProperty.Prop_ID = propid
    else
    select 0

    Thanx
    Sampangi


     
    procedure is 
    this recordset but 
    it did not 
    JOIN 
    dbo.tblProperty.Prop_Region 
    *** 
    sampangi Guest

  3. #3

    Default Re: Dropping a recordset during a stored procedure

    That will work. Thanks much.

    Mia J.

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

Similar Threads

  1. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 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. Suppressing second recordset from stored procedure
    By A_X_L_V in forum ASP Database
    Replies: 5
    Last Post: November 6th, 09:54 PM
  4. Stored Procedure vs. SQL Through Recordset.
    By craig v in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 03:15 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