Setting default values for my SP's parameters

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)

Setting default values for my SP's parameters

Author

Topic

daniel.newman@bis-web.net
Yak Posting Veteran

71 Posts

Posted - 2001-10-22 : 05:25:58

Hello,

I have the following SP, but it's not letting me include the GetDate function within my parameter declarations.

This is what I have:


CREATE PROCEDURE dbo.admin_GetAllJobs
	@Archive bit = 0,
	@searchString varchar(10) = GetDate
AS

DECLARE @searchDate datetime
DECLARE @searchMonth int
DECLARE @searchYear int

SELECT @searchDate = CONVERT(datetime, @searchString)
SELECT @searchMonth = DATEPART(month, @searchDate)
SELECT @searchYear = DATEPART(year, @searchDate)

IF @Archive = 1
	SELECT V.RefNo, V.JobTitle, L.LocationName, V.whShort, 
		V.typeID, V.ExpDate, V.locSpecific, V.locID 
	FROM JobVacancies V
		INNER JOIN Locations L ON V.locID = L.[ID]
	WHERE DATEDIFF(month, V.ExpDate, @searchMonth) = 0
		AND DATEDIFF(year, V.ExpDate, @searchYear) = 0
		AND V.Archived = @Archive
	ORDER BY V.RefNo
ELSE
	SELECT V.RefNo, V.JobTitle, L.LocationName, V.whShort, 
		V.typeID, V.ExpDate, V.locSpecific, V.locID 
	FROM JobVacancies V
		INNER JOIN Locations L ON V.locID = L.[ID]
	WHERE DATEDIFF(month, V.ExpDate, @searchMonth) >= 0
		AND DATEDIFF(year, V.ExpDate, @searchYear) >= 0
		AND V.Archived = @Archive
	ORDER BY V.RefNo
GO

But it keeps complaining of "Syntax error converting datetime from character string."

Am I doing this wrong, or is there something stupid I haven't done yet?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources