Solved

Sending Greater Than and Less than with Date Parameters

Posted on 2007-11-27
3
2,544 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 59
Change variables in SQL table 6 81
How to query LOCK_ESCALATION 4 40
SQL Restore Script - Syntax Error 8 87
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 …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now