ASP/SQL Stored Procedure/ASP function error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ASP/SQL Stored Procedure/ASP function error

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2007-07-20 : 08:35:27

Hey I have the following Stored Procedure


CREATE PROCEDURE spGetOrderCount
(
	@search varchar(1000) = default
)

AS

SET NOCOUNT ON

/* Setup search string */
IF (@search <> '')
BEGIN
	SET @search = 'WHERE' + @search
END


/* Create a temporary table */
CREATE TABLE #TempTable
(
	row int IDENTITY,
	totalCount int
)

/* Insert the search results into query */
EXEC
(
	'INSERT INTO #TempTable([totalCount])' +
	'SELECT COUNT(*) AS totalCount ' +
	'FROM tblOrders' + @search 
)

/* Extract the wanted records from the temporary table */
SELECT	[totalCount],
			RecordsLeft = 
			(
				SELECT COUNT(*) 
				FROM #TempTable TI
			) 
FROM		#TempTable

SET NOCOUNT OFF

RETURN
;
GO

And then the following function which specifies the where clause of the statement


function getOrderCount(strDate, strStatusList)
getOrderCount = 0
dim objRS, objSP, strWhereClause

if isDate(strDate) and len(strStatusList) > 0 then
'# Filter on order status
			
		
			
'# Filter on date clause
strWhereClause = "(tblOrders.orderDate >= " & sqlServerDate(strDate) & ")"
			
'#	GET order count
Set objSP = SQLGetProcedure("spGetOrderCount")
SQLSetProcedureParam objSP, "search", strWhereClause

Set objRS = SQLExecuteProcedure(objSP)
				
if not objRS.eof then
getOrderCount = objRS("totalCount")
end if
		
'# Free resources
deleteRecordset(objRS)
deleteObject(objSP)		
end if

	end function

When I do this together I get the following error on my ASP Page

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '.'.

/sigma_eircommobdispatch/server/database.asp, line 235

And the print out of the resulting string from the function is


(tblOrders.orderDate >= CONVERT(DATETIME, '2007-7-13', 102))

If i remove the Where clause the statement works fine..

Any ideas

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 11:21:33

And what is the search condition you are passing through the app to the @Search parameteR?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources