Executing Stored Procedures based on condition in Razor Pages

Question

Hi,

I have a function that executes two stored procedures based on a condition.

Stored procedure for the first condition works fine, but stored procedure for the second condition fails.

public async Task<JsonResult> OnGetGetAvailableSlotsAsync(string ActivityName, string BookedDate, string Duration)
        {
            Console.WriteLine(Duration);
            DateTime BookingDate = Convert.ToDateTime(BookedDate);
            ActivityName = ActivityName.ToString();
            List<HourlyBasedView> LstPreferredTimeSlot = new List<HourlyBasedView>();            
            if (Duration == "1 Hour")
            {
                LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("EXEC dbo.AvailableHourSlots @BookedDate = {0}, @ActivityName = {1}", BookingDate, ActivityName).
                    AsNoTracking().ToList();
                if (LstPreferredTimeSlot.Count == 0)
                {
                    LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("Select HourlyBasedTime from HourlyBased order by id").AsNoTracking().ToList();
                }
            }
            else if (Duration == "Half An Hour")
            {
                LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("EXEC dbo.AvailableHalfAnHourSlots @BookedDate = {0}, @ActivityName = {1}", BookingDate, ActivityName).
                    AsNoTracking().ToList();
                if (LstPreferredTimeSlot.Count == 0)
                {
                    LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("Select HalfAnHourTime from HalfAnHour order by id").AsNoTracking().ToList();
                }

            }
            return new JsonResult(LstPreferredTimeSlot);
        }

I want to get the available slots by comparing the tables.

Do i need to create another new list for my second procedure?

This is my frontend function where i am displaying the fetched result.

function GetAvailableSlots(e) {
        var rootPath = '@Url.Content("~")';
        alert("Hi");
        var ActivityList = document.getElementById("DrpDwnActivityList");
        var ActivityListValue = ActivityList.options[ActivityList.selectedIndex].value;
        var BookedDate = document.getElementById("DateBookedDate").value;

        $.ajax({
            type: "Get",
            url: rootPath + "/ManageBooking/Create?handler=GetAvailableSlots",
            headers: { "RequestVerificationToken": $('input[name="__RequestVerificationToken"]').val() },
            data: { Duration: e.value, ActivityName: ActivityListValue, BookedDate: BookedDate },
            dataType: "json",
            success: function (result) {
                console.log(result) ; 
                $("#PreferredTimeSlot").css("display", "block"); //clear the container.
                $.each(result, function (key, value) {
                    $("#PreferredTimeSlot").append(`<input type="radio" class="form-check-inline" value="${value.hourlyBasedTime}" name="ManageBooking.PreferredTimeslot" style="margin-right:6px;"/>${value.hourlyBasedTime} &nbsp;`);
                });
                alert(result);
            },
            error: function (data) {
                alert(data);
            }
        });
    }

Any help would be appreciated!

Answer 1

Hi @Rena Ni - MSFT & @Olaf Helper ,

I managed to fix this issue. The column name for the halfanhour based table name was not same as the view i created, once i rename the column its executing. And i just modified my Stored Procedures into a single stored procedure like this,

CREATE PROCEDURE [dbo].[sp_AvailableTimeSlots]
 @Duration varchar(MAX),
 @ActivityName varchar(MAX),
 @BookedDate datetime2(7)

 AS

 IF(@Duration = '1 Hour')
	BEGIN
		SELECT distinct HourlyBasedTime 
		FROM   ManageBooking as mb, HourlyBased as hb  
		WHERE bookingdate = @BookedDate and ActivityName = @ActivityName
		and (substring(HourlyBasedTime,1,2) <> substring(mb.PreferredTimeslot,1,2))
	END

 ELSE IF(@Duration = 'Half An Hour')
	BEGIN
		SELECT distinct HourlyBasedTime
		FROM ManageBooking as mb, Halfanhour hh
		WHERE  bookingdate = @BookedDate
		and ActivityName = @ActivityName 
		and (substring(hh.HourlyBasedTime,1,5) <> substring(mb.PreferredTimeslot,1,5))
		and (right(hh.HourlyBasedTime,5) <> right(mb.PreferredTimeslot,5))
	END


GO