Solved

Sending Greater Than and Less than with Date Parameters

Posted on 2007-11-27
3
2,546 Views
Last Modified: 2008-02-01
Hi all,
 I am trying to construct a parametersed query. I have constructed a stored prodedure and an asp.net class which seems to be doing everything i need. the only last problem i have is with the dates. This is the stored procedure:

ALTER PROCEDURE [dbo].[SelectApplications] (@titleFilter  varchar(50),@appType varchar(3), @programme varchar(3),@status varchar(3), @startDate varchar(15),@endDate varchar(15),@department varchar(30))
AS
declare @sql varchar(2000)
BEGIN
set @sql = 'select top 100 * from applications where
app_type like ''' + @appType  + ''' and app_title1 like ''%' + @titleFilter + '%'' and
progid like  ''' + @programme + ''' and
app_status like ''' + @status + ''' and
startDate ' + @startDate + ' + and
endDate + ' + @endDate + ' +  and
DeptId like ''' + @department + ''' '
exec (@sql)
END

My asp.net class sends the following code to the stored procedure:
exec SelectApplicationsView @titleFilter='',@appType='%',@programme='%',@Status='%',@startDate='< 1/1/2000',@endDate='> 1/1/1900',@department='%'

as you can hopefully see, my problem is that the dates are being put in single quotes when in actual fact they shouldn't be in single quotes.

do you know how to acheive this in dynamic sql?
or should i be doing this query another way?

thanks!
crich


0
Comment
Question by:crich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20360804
use BETWEEN
0
 
LVL 18

Accepted Solution

by:
Yveau earned 200 total points
ID: 20362235
Change it to:

Hope this helps ...
ALTER   PROCEDURE [dbo].[SelectApplications] 
(       @titleFilter  varchar(50)
,       @appType varchar(3)
,       @programme varchar(3)
,       @status varchar(3)
,       @startDate varchar(15)
,       @endDate varchar(15)
,       @department varchar(30))
AS
declare @sql varchar(2000)
BEGIN
		set @sql = 'select top 100 * from applications where app_type like ''' 
		+ @appType  + ''' and app_title1 like ''%' 
		+ @titleFilter + '%'' and progid like  ''' 
		+ @programme + ''' and app_status like ''' 
		+ @status + ''' and startDate ' 
		+ @startDate + ' and endDate ' 
		+ @endDate + ' and DeptId like ''' 
		+ @department + ''' ' 
		exec (@sql)
END
 
 
--

Open in new window

0
 

Author Comment

by:crich
ID: 20364586
hi thanks for the replies. i will have to give points to Yveau as the solution built upon my code even though i expect i could have tried between. i hope this is ok...

Thanks a lot for your help, it worked fine! I had to add some convert statements too..

richard
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 71
Why is the output of this function is like this? 4 44
Bubble user-defined Sql RAISERROR(...) to c# exception 14 206
SQL- GROUP BY 4 25
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question