Pasing Parameters to a stored procedure....

[Mayoor]

The following stored procedure

CREATE PROCEDURE dbo.sp_return_applications_first_april

@Year int

AS

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

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "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


gives me the following error when Im trying to execute it from an ASP page...

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'.

/apply/reporting/report.asp, line 50


Please help!!!

 

[markVII]

If you take another look, you'd see that at the end
your @SQLStatement variable holds only PART of a select statement, namely:
"from Applicants where convert..."

Don't you agree that it ought to be "SELECT * (or something) from Applicants where convert..."

 

[SQLBill]

This is what your SQL command becomes:

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

Try to run that in Query Analyzer and it will fail for the obvious reason....there's no SELECT statement.

You need something like:

SELECT @SQLStatement = "select col1, col2 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"

-SQLBill

 

[Mayoor]

ok

CREATE PROCEDURE dbo.sp_return_applications_first_april

@Year int

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement 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


its now saying.......

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

 

[SQLBill]

That's right. No where are you providing the information for the variable @year.

EXEC(@SQLStatement)

I believe the proper syntax is:

EXEC(@SQLStatement) @year = 2004

-SQLBill

 

[Mayoor]

Bill I am passing the parameter in from my ASP page using..

objCommand.Parameters.Append objCommand.CreateParameter("@intCurrentYear", adVarchar, adParamInput,10, intCurrentYear)

the value @Year should be populated with the passed value. Any ideas what Im doing wrong?

 

[SQLBill]

I might be misunderstanding you, but bear with me:

You are creating a procedure:

CREATE PROCEDURE dbo.sp_return_applications_first_april

  @Year int

Try this:

CREATE PROCEDURE dbo.sp_return_applications_first_april

  @Year int = 2004

AS

That will set @Year to a default of 2004.

-SQLBill

 

[Mayoor]

Bill - that is still not working. Im still getting the same error, cant think what it could be. The stored procedure now looks like this

CREATE PROCEDURE dbo.sp_return_applications_first_april

(
@Year int = 2004

)

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

 

[panayotisk]

The problem is with EXEC: you are trying to execute a string that contains variables and you do not provide the EXEC call with the declarations of these variables. @Year cannot be recognized in the EXEC unless you declare it.

You might try

SET @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"

I haven't tested it though

Also prefer EXEC sp_executesql for dynamic execution than a simple EXEC.