Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dynamic SQL Dates problem...

Author  Topic 

james_w
Starting Member

21 Posts

Posted - 2010-02-24 : 13:21:29
Hi all,

I'm having a problem getting the PROC below to work.




ALTER PROC spTest

@fromDate datetime,
@toDate datetime,
@cid nvarchar(300)

AS

DECLARE @dynamicdistancesql varchar(4000)

SET @dynamicdistancesql = 'SELECT * FROM [CaseReg]
WHERE [ClientID] IN (' +@cid+ ')
AND ([RegistrationDate] >=' +@fromDate+ ')
AND ([RegistrationDate] <=' +@toDate+ ')'

Exec (@dynamicdistancesql)


The error i'm getting is: Conversion failed when converting datetime from character string.

I've tried having the date variables as nvarchar and using cast like:

ALTER PROC spTest

@fromDate nvarchar(50),
@toDate nvarchar(50),
@cid nvarchar(300)

AS

DECLARE @dynamicdistancesql varchar(4000)

SET @dynamicdistancesql = 'SELECT * FROM [CaseReg]
WHERE [ClientID] IN (' +@cid+ ')
AND ([RegistrationDate] >=' +CAST(@fromDate As datetime)+ ')
AND ([RegistrationDate] <=' +CAST(@toDate As datetime)+ ')'

Exec (@dynamicdistancesql)


I still get the same error message.

I could fix the date problem by not using a dynamic query but I had to go down this route to get the IN clause to work correctly as this value will be a CSV such as 8,23,34,45,54.

Anybody got any suggestions?

Many thanks for any help provided.

Regards,

James.

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 13:31:10
[code]SET @dynamicdistancesql = 'SELECT * FROM [CaseReg]
WHERE [ClientID] IN (' +@cid+ ')
AND ([RegistrationDate] >=''' +CONVERT(varchar(24), @fromDate, 126) + ''')
AND ([RegistrationDate] <=''' +CONVERT(varchar(24), @toDate, 126)+ ''')'
[/code]
but you are right, you do not want to be using dynamic SLQ - opens you up to SQL injection, you need Select permission on the table itself, not just Execute Permission on the SProc, and so on.
[code]
SELECT *
FROM [CaseReg]
WHERE ',' + @cid + ',' LIKE '%,' + CONVERT(varchar(20), [ClientID]) + ',%'
AND ([RegistrationDate] >= @fromDate
AND ([RegistrationDate] <= @toDate
[/code]
EDIT: I have assumed ClientID is INT, therefore it needs converting to VARCHAR
Go to Top of Page

james_w
Starting Member

21 Posts

Posted - 2010-02-24 : 15:36:29
Not to sure how that exactly works but it seems to, thanks very much.
Go to Top of Page

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-24 : 15:43:52
What you were doing originally was Casting a Datetime back to a datetime. Dynamic SQL was reading your parameter still as Datetime, you needed to convert it to a text string.

Convert(Varchar(24), @ , 126) is taking your parameter and turning it into a Varchar(24) string instead.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 22:25:38
... and the second one is taking your @cid and putting commas around it, so lets say your parameter is

8,23,34,45,54

then ',' + @cid + ',' will make

,8,23,34,45,54,

and then we are doing '%,' + CONVERT(varchar(20), [ClientID]) + ',%' so if [ClientID] was, say, 34 that would give you

%,34,%

so the LIKE then becomes

',8,23,34,45,54,' LIKE '%,34,%'

which, in this case, will be True.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 02:24:27
If [RegistrationDate] has time too,

SELECT *
FROM [CaseReg]
WHERE ',' + @cid + ',' LIKE '%,' + CONVERT(varchar(20), [ClientID]) + ',%'
AND ([RegistrationDate] >= @fromDate
AND ([RegistrationDate] < dateadd(day,1,@toDate)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:26:21
dateadd(day,1,@toDate)

may perhaps need to be:

DATEADD(Day, DATEDIFF(Day, 0, @toDate), 1) ??
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 02:56:01
quote:
Originally posted by Kristen

dateadd(day,1,@toDate)

may perhaps need to be:

DATEADD(Day, DATEDIFF(Day, 0, @toDate), 1) ??



Only if @toDate has time too

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 03:01:57
" Only if @toDate has time too"

Ah! Good point! Most likely it doesn't.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 03:21:00
quote:
Originally posted by Kristen

" Only if @toDate has time too"

Ah! Good point! Most likely it doesn't.


Yes becuase usually people dont send time part when sending date ranges

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

james_w
Starting Member

21 Posts

Posted - 2010-02-25 : 04:37:03
Thanks very much Kristen.

I was a little unsure when looking at your query as I thought if my parameter was 1,2,3,4,5 then i would also return client id's 11,12 etc but I understand now why it wouldn't and what the '%,' & ',%'is for.



Thanks again, and also Madhivanan.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 05:39:59
Its a useful little trick
Go to Top of Page

james_w
Starting Member

21 Posts

Posted - 2010-02-25 : 07:32:20
Hmmm, seem to be having a slight problem when displaying the data on the website.

It works fine when I declare the variables and set them within SQL but when displayed from my record set on a webpage it will only display the records that apply to the first ClientID that is selected?!?!

Any ideas?

James.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 07:44:04
Can you "catch" the exact SQL sent to the server?

That will, no doubt, reveal the cause - if not immediately obvious then post it here.

The solution is a bit fragile in terms of any embedded spaces ... if that looks like the issue then perhaps this:

WHERE ',' + REPLACE(@cid, ' ', '') + ',' LIKE '%,' + CONVERT(varchar(20), [ClientID]) + ',%'
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 07:45:59
quote:
Originally posted by Kristen

Can you "catch" the exact SQL sent to the server?

That will, no doubt, reveal the cause - if not immediately obvious then post it here.

The solution is a bit fragile in terms of any embedded spaces ... if that looks like the issue then perhaps this:

WHERE ',' + REPLACE(@cid, ' ', '') + ',' LIKE '%,' + CONVERT(varchar(20), [ClientID]) + ',%'



or @CID might have new line characters,tab etc


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

james_w
Starting Member

21 Posts

Posted - 2010-02-25 : 07:53:20
how would I go about catching what is past to SQL? (within SQL i mean, if possible, i could replicate whats passed from a web page easily) That would be very handy, oftern wondered that.

I know that the form object that passes the client id's is sending it as '32, 34' and not '32,34' so it's adding spaces, I can easily get rid of those spaces before it's passed to SQL.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 09:08:20
I have three ways:

My website sends all commands through a central function. That has the ability to print the exact SQL at the bottom of the web page (debug mode)

In the DB I have a "logging" table. The Sproc can concatenate all the parameters (I separate them with "~" which almost never appears in real data) [you have to convert any non-varchar parameter to varchar in order to concatenate them), and then INSERT that into the logging table.

Or you can use SQL Profiler. Use your SQL Login to connect it to your server and it will grab everything that is fired at the server. If you have a busy server get the web site and SQL profiler ready ... press GO on SQL profiler, immediately Submit your web form, and then Stop the trace in SQL Profiler.

You can set up Filters in SQL Profiler so it only records your connection / your application / your database, etc., but I find they take a bit of messing around, so you may want to try my earlier "be ready and do it quick" method first!
Go to Top of Page

james_w
Starting Member

21 Posts

Posted - 2010-02-25 : 09:54:47
Thanks for the info, i'll look into SQL profiler when I have some spare time or maybe create something like you mentioned.

By the way, once I took out the spaces the web page dispayed all the correct data :).

Thanks again, been really helpful.
Go to Top of Page
   

- Advertisement -