Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sending Greater Than and Less than with Date Parameters

Posted on 2007-11-27
3
Medium Priority
?
2,551 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 800 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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