Trouble executing stored procedure from my ASP page

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trouble executing stored procedure from my ASP page

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-03 : 08:02:44

Mayoor writes "Hi I am trying to execute the following stored procedure from my asp page

The asp page is passing a value "@Year" to the stored procedure ut seems to fall over with a declaration problem. Here is the stored procedure

CREATE PROCEDURE dbo.sp_return_applications_first_april

(
@Year varchar(10)

)

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
DECLARE @intCurrentYear varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT Count(ID) from Applicants where convert(datetime, datefinished, 101) Between Cast('04/01/' + Cast(@Year)as varchar) as DateTime)) AND convert(datetime, GETDATE(), 101) AND datefinished IS NOT NULL"

-- Execute the SQL statement
EXEC(@SQLStatement)
GO

and the error im getting from the browser is...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@Year'.

/apply/reporting/report.asp, line 50

Please can you help!

Mayoor"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 08:13:42

something like

SELECT @SQLStatement = 'SELECT Count(ID) from Applicants where convert(datetime, datefinished, 101)
Between Cast(''' + cast(@Year as varchar(4)) + '0401' + ''' as DateTime) AND convert(datetime, GETDATE(), 101) AND datefinished IS NOT NULL'

convert(datetime, GETDATE(), 101)
What is this trying to do (it's not doing what you expect whatever that is)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-03 : 08:23:48

Why would you use dynamic SQL here. It's inefficient, a security risk, and completely unnecessary.

Cursors and dynamic SQL are usually just a sign of something not being well thought out. The problem is that the system can pay heavily for it. There are cases you need them, but they are rare.

CREATE PROCEDURE dbo.sp_return_applications_first_april

@Year varchar(10)

AS

SELECT
Count(ID)
FROM Applicants
WHERE
CAST(datefinished AS DATETIME) BETWEEN
CAST('04/01/' + CAST(@Year)AS VARCHAR) AS DATETIME))
AND GETDATE()
AND datefinished IS NOT NULL

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-04 : 15:24:27

First, troubleshoot your stored procedure by running it from Query Analyzer. This isolates the issues. Once the stored procedure is working fine, then run it from your ASP code and troubleshoot the ASP code's calling the procedure.

And Derrick, nice removal of Dynamic SQL.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources