If I execute the stored procedure in Query Analyzer like this.
sp_mars_search 't', 1, '5747','','','',10
I get the following error
"Syntax error converting datetime from character string."
If I comment out the two lines in the CASE that indicate @strDate the sp executes perfectly.
I've tried CAST and CONVERT both already.
I've also tried passing a date through.
I'm at a lost.
I'm also new to SP.
Here is my stored procedure.
CREATE PROCEDURE sp_mars_search
(
@strWhere VarChar(800),
@intRecord Int,
@strTracking VarChar(80),
@strName VarChar(80),
@strDate Datetime,
@strLocation VarChar(80),
@intRecordsPerPage Int
)
AS
SET NOCOUNT ON
IF @strDate IS NULL SELECT @strDate = GETDATE()
DECLARE @strWhereClause varchar(255)
SELECT @strWhereClause =
CASE @strWhere
WHEN 't' THEN '[Tracking No:] like "%' + @strTracking + '%"'
WHEN 'n' THEN '[To:] Like "%' + @strName + '%"'
WHEN 'd' THEN '[Date:] = "' + @strDate + '"'
WHEN 'l' THEN '[Location:] = "' + @strLocation + '"'
WHEN 'tn' THEN '[Tracking No:] like "%' + @strTracking + '%" and [To:] Like "%' + @strName + '%"'
WHEN 'td' THEN '[Tracking No:] like "%' + @strTracking + '%" and [Date:] = "' + @strDate + '"'
WHEN 'tl' THEN '[Tracking No:] like "%' + @strTracking + '%" and [Location:] = "' + @strLocation + '"'
END
CREATE TABLE #TempMars
(
tMarsID Int IDENTITY,
marsID Int,
[To:] VarChar(50),
[Tracking No:] VarChar(255),
[Date:] DateTime,
[Location:] varchar(50),
[Phone No:] varchar(50),
[From:] varchar(50),
[Rec Location:] varchar(50),
[Rec Phone No:] varchar(50)
)
DECLARE @SearchSQL varchar(255)
SELECT @SearchSQL = 'INSERT INTO #TempMars SELECT * FROM marsPTS WHERE' + @strWhereClause
EXECUTE(@SearchSQL)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@intRecord -1) * @intRecordsPerPage
SELECT @LastRec = (@intRecord * @intRecordsPerPage + 1)
DECLARE @CountSQL varchar(255)
SELECT @CountSQL = 'SELECT MoreRecords = COUNT(*)FROM #TempMars WHERE' + @strWhereClause
EXECUTE(@CountSQL)
SELECT * FROM #TempMars WHERE tMarsID > @FirstRec AND tMarsID < @LastRec
SET NOCOUNT OFF
I'm offereing all the points I have left. After the first of the month I'll have more (I qualified for KPro :-))
