I am doing something that is, I am sure, very simple, but I am having a hard time wrapping my head around how to do it. I have a legacy site that is in classic ASP. I am trying to call a stored procedure that will return all the rows back to the page and display them. I have to use a stored procedure (I believe) because the query uses a dynamic pivot command.
Here is the stored procedure:
SET NOCOUNT ON;
declare @cmd varchar(max)
declare @columns varchar(max)
set @columns = ''
select @columns = @columns + '['+CAST(MeetingDate AS CHAR(10))+'],' from morning_call_dates WHERE Display = 1 order by MeetingDate asc
select @columns = substring(@columns, 0, len(@columns))
SET @cmd = '
PIVOT(MAX(Entry) FOR MeetingDate IN ('+@columns+')) AS MeetingDate'
When I execute this on the server I get a nice recordset that looks like this:
MeetingAttendee 2012-12-02 2013-12-03 2013-12-04 2013-12-05
Smith No No Yes No
Jones Yes No No Yes
Adams Yes Yes No Yes
So my questions is how do I call the procedure from classic ASP to return the data above so that I can display it on my web page. I know how to do this with a regular ADO call to the server using a query but not using a stored procedure.
Thanks in advance for your help.