|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
Hi, I am having problem with a stored proc. Basically im building a string to run a search
on the database depending on what parameters have had values passed to them. The problem is that the code regarding the date throws this error - Microsoft OLE DB Provider for SQL Servererror '80040e07' Syntax error converting datetime from character string. here's the stored procedure code (problem code is in red)- Code:
CREATE PROCEDURE [dbo].[usp_Forms_GetSearchResults]
@surname varchar(100),
@outletid int,
@formtype int,
@lodgementdate datetime,
@assignedto varchar(50),
@formstatus varchar(10),
@userID varchar(8)
AS
declare @sqlstr varchar(1999)
select @sqlstr = 'Select * From tblFormsManagement where 1=1'
if @surname<>''
begin
if patindex('%*%',@surname)>0
select @sqlstr = @sqlstr + ' and tblFormsManagement.ClientLName like ''' + replace(@surname,'*','') + '%'''
else
select @sqlstr = @sqlstr + ' and tblFormsManagement.ClientLName = ''' + @surname + ''''
end
if @outletid<>0
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.OutletId = ' + convert(varchar,@outletid)
end
if @formtype <> 0
begin
if @formtype =1
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientIntakeForm"'
end
if @formtype =2
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientExitForm"'
end
if @formtype =3
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "DPI"'
end
if @formtype =4
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "13Week"'
end
if @formtype =5
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "DMICaseBase"'
end
if @formtype =6
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientSuspensionForm"'
end
if @formtype =7
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ReturnfromSuspensionForm"'
end
if @formtype =8
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientDetailsForm"'
end
if @formtype =9
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "HighCost"'
end
end
if @lodgementdate <> null
begin
if @lodgementdate<>''
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.CreatedDate = ' + @lodgementdate
end
end
if @assignedto <> ""
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.AssignedTo = ''' + @assignedto + ''''
end
if @formstatus <> ""
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormStatus = ''' + @formstatus + ''''
end
execute (@sqlstr + ' Order by OutletName')
GO
Any help would be great. Im sure its something simple but I don't have enough experience with stored procs to figure it out myself and I haven't been able to find anything on the net to help. Thanks |
|
#2
|
|||
|
|||
|
Hi adamtibballs,
There are couple of things: First of all, @lodgementdate is of type datetime and cannot be concatinated with the rest of the string directly. You need to convert it to varchar. Secondly, while contactinating, your converted date should appear in quotes('). So your statement would be like this: select @sqlstr = @sqlstr + ' and tblFormsManagement.CreatedDate = ''' + CONVERT(varchar(20), @lodgementdate) + '''' Hope this solves the problem. Regards, Vikram |
|
#3
|
|||
|
|||
|
Thanks Vikram! All working now.
Quote:
|
![]() |
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Problem searching for a date using stored procedure |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|