Stored Proc returns nothing ???

[mikeyd]

Below is the Stored Procedure.

In Query Analyser it returns results and when using .aspx.


BUT in ASP it's returning NO results ?

----ASP---
*********************************************************
<!-- #INCLUDE VIRTUAL=&quot;/padmin/modification_search/ds.asp&quot; -->
<%CALL open()

cmd.CommandText = &quot;sp_DATE&quot;
cmd.Execute

SET rs = cmd.Execute
Call Clear(Cmd)

response.write rs.fields.count
'response.write rs(0)

CALL close()%>


--SQL Stored Procedure---
***********************************************************

CREATE PROCEDURE sp_DATE

AS

--DROP TABLE #tempTable
CREATE TABLE #tempTable(codesection NVARCHAR(400),sectiontitle NVARCHAR(400),implementationtype NVARCHAR(400),implementationdate SMALLDATETIME,modificationnumber NVARCHAR(400),
modificationtitle NVARCHAR(400),version NVARCHAR(400));

DECLARE colnames_cursor CURSOR
FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Modification_Details'
AND COLUMN_NAME LIKE 'BSC%'
OPEN colnames_cursor
DECLARE @colname varchar(80)
FETCH NEXT FROM colnames_cursor INTO @colname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @colname = RTRIM(@colname)
INSERT #tempTable EXEC ('
IF (LEN('''+@colname+''') = 11)
BEGIN
SELECT [Code Section],[Section Title],ImplementationType,ImplementationDate,ModificationNumber, ModificationTitle,
'+@colname+' FROM Modification_Details,code_sections
WHERE '+@colname+' IS NOT NULL
AND code_sections.[Code Section] = RIGHT('''+@colname+''',1)
AND ImplementationDate > ''11/12/2001''
AND [Code Section] = ''A''
END
ELSE
BEGIN
SELECT [Code Section],[Section Title],ImplementationType,ImplementationDate,ModificationNumber, ModificationTitle,
'+@colname+' FROM Modification_Details,code_sections
WHERE '+@colname+' IS NOT NULL
AND code_sections.[Code Section] = STUFF(RIGHT('''+@colname+''',2),1,0,''-'')
AND ImplementationDate > ''11/12/2001''
AND [Code Section] = ''A''
END
' )
PRINT ' '
END
FETCH NEXT FROM colnames_cursor INTO @colname
END
SELECT TOP 1 version, codesection, ImplementationType,ImplementationDate,ModificationNumber, ModificationTitle
FROM #tempTable
ORDER BY version DESC
;

DROP TABLE #tempTable
CLOSE colnames_cursor
DEALLOCATE colnames_cursor
GO

 

[swampBoogie]

Add

SET NOCOUNT ON

at the beginning of the procedure