Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Question Unanswered: Return Result Set from Stored Procedure

    here's another... probably easy question.

    Okay, I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.

    But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?

    I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.

    (fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)

    I'm sure there are many different ways to do this, and I'm fairly certain I could hack some awful-looking thing together if I tried long enough, but I'm kind of wanting to learn good practices and not write code in a way that makes me look like a drooling amateur. Please have mercy.

    One beer for the developer out there who helps me successfully be less of a drooling amateur. I appreciate your help and assistance.

    Last edited by asherman86; 12-08-11 at 17:05.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    CREATE PROC MyProc
    AS
    SELECT * FROM MyTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32
    Quote Originally Posted by Thrasymachus View Post
    CREATE PROC MyProc
    AS
    SELECT * FROM MyTable
    Really... Is that all?? <_< Am I really thinking this thing way too hard again?

    So if I said...

    Code:
    Imports System.Data.SqlClient
    
    Public Function GetMyDarnTable() As Dataset
    
         Dim Adataset As Dataset = 'call that stored procedure from code here.
         
         Return Adataset
    
    End Function
    That would ... get me the results?

  4. #4
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    What I dun

    I made it work!!! Looky looky!!!

    Code:
    USE [ClaytorsCottages]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_getAvailableRooms]    Script Date: 12/08/2011 16:50:23 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[proc_getAvailableRooms]
    	@Begin DATE,	--User's specified Check-in Date
    	@End DATE,		--User's specified Check-out Date
    	@Jac BIT,		--Boolean indicating user would like a room with jacuzzi
    	@Priv BIT,		--Boolean indicating user would like a room with private access
    	@Fire BIT,		--Boolean indicating user would like a room with a fireplace
    	@Bed NVarchar(6)--Indicates King, Queen, or Double bed type
    	
    AS
    BEGIN
    	BEGIN
    		--Consolidate the AvailableRooms table and GuestReservations table
    		--such that I can choose from all the rooms and their reservations
    		--dates, whether the room is currently reserved or not. 
    		SELECT  tblAvailableRooms.AvailID, tblGuestReservations.ReservationID, 
    				tblGuestReservations.ResBegin, tblGuestReservations.ResEnd
    		INTO #tmpMatches
    		FROM tblAvailableRooms
    		LEFT JOIN tblGuestReservations
    		ON tblAvailableRooms.AvailID=tblGuestReservations.AvailID
    		ORDER BY tblGuestReservations.ReservationID DESC
    	END
    	
    	BEGIN
    		--Select all of the available rooms for which the Reservation
    		--begin and end dates are either NULL, or do not fall between
    		--the user's desired begin and end dates. 
    		SELECT * INTO #tmpResults FROM #tmpMatches 
    		WHERE ((#tmpMatches.ResBegin NOT BETWEEN @Begin AND @End)
    		AND (#tmpMatches.ResEnd NOT BETWEEN @Begin AND @End))
    		OR (#tmpMatches.ReservationID IS NULL)
    	END
    	
    	--Select all of the available rooms for which the user's begin
    	--and end dates do not fall between any current reservation dates
    	--as well as those that are null
    	BEGIN
    		SELECT AvailID, ReservationID INTO #tmpFinalResults
    		FROM #tmpResults
    		WHERE ((@Begin NOT BETWEEN ResBegin AND ResEnd) 
    		AND (@End NOT BETWEEN ResBegin AND ResEnd))
    		OR (ReservationID IS NULL)
    	END
    	
    	
    	--Call stored procedure stored procedure ConsolidateRoomChoice
    	--This procedure is supposed to narrow the selected records
    	--based on the user's specified amenities. 
    	CREATE TABLE #FinalResults
    	(
    		AvailID INT,
    		Jacuzzi BIT,
    		PrivateAccess BIT,
    		Fireplace BIT,
    		BedType NVarchar(6)
    	)
    	
    	INSERT #FinalResults EXEC [dbo].[proc_ConsolidateRoomChoices]
    			@Jacuzzi = @Jac,
    			@Private = @Priv,
    			@Fireplace = @Fire,
    			@BedType = @Bed
    			
    	SELECT * FROM #FinalResults
    	
    	RETURN
    END

    Code:
    USE [ClaytorsCottages]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_ConsolidateRoomChoices]    Script Date: 12/08/2011 16:51:04 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[proc_ConsolidateRoomChoices]
    /*
    NAME: proc_ConsolidateRoomChoices
    Description: Get the final result set of available rooms matching
    				the user's exact specification. 
    Author: Amber Sherman
    Modification Log: Change
    
    Description								Date		Changed By
    Created procedure						11/30/2011	Amber Sherman
    */
    -----------------------------------------------------------------------
    	@Jacuzzi BIT,
    	@Private BIT,
    	@Fireplace BIT,
    	@BedType NVarchar(6)	
    AS
    BEGIN
    		BEGIN
    				--Get the AvailID, the BedID, and the RoomTypeID
    				--and stuff them into a temporary table so I have 
    				--something organized to choose from. 
    				SELECT B.AvailID, A.BedID, A.RoomTypeID 
    				INTO #tempA
    				FROM tblAvailableRooms AS A
    				INNER JOIN #tmpFinalResults AS B
    				ON A.AvailID = B.AvailID
    					
    				--Join the temporary table, the room types, and the bed times
    				--along with all desired amenities, to be narrowed down.
    				SELECT A.AvailID, B.Jacuzzi, B.PrivateAccess, B.Fireplace, C.BedType
    				INTO #tempB
    				FROM tblRoomTypes As B
    				INNER JOIN #tempA AS A ON A.RoomTypeID = B.RoomTypeID
    				INNER JOIN tblBedTypes AS C ON A.BedID = C.BedID
    				ORDER BY A.AvailID
    				
    				--Create a table variable to put the result set into
    				DECLARE @Final TABLE 
    				(
    					AvailID INT,
    					Jacuzzi BIT,
    					PrivateAccess BIT,
    					Fireplace BIT,
    					BedType NVarchar(6)
    				) 
    				
    				--Fill the table variable with results from the temporary table
    				--that exactly match the user's specifications
    				INSERT INTO @Final(AvailID, Jacuzzi,PrivateAccess, Fireplace, BedType)
    					SELECT * FROM #tempB
    					WHERE(Jacuzzi = @Jacuzzi AND PrivateAccess = @Private
    						AND Fireplace = @Fireplace AND BedType = @BedType)
    					ORDER BY AvailID
    
    				SELECT * FROM @Final			
    			RETURN
    		END
    END

    When I feed it variables, it returns exactly what it's supposed to! I've been working non-stop on this for two days!!!

    I'd really like some feed back, comments, etc on how this looks, the functionality part, if there's any way to make it more efficient or better. I'm happy it works, but I know there's always room for improvement!

    Thanks everyone! your help has been very appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •