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.
| 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)ASDECLARE @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)ASDECLARE @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] >= @fromDateAND ([RegistrationDate] <= @toDate[/code]EDIT: I have assumed ClientID is INT, therefore it needs converting to VARCHAR |
 |
|
|
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. |
 |
|
|
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. |
 |
|
|
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,54then ',' + @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. |
 |
|
|
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] >= @fromDateAND ([RegistrationDate] < dateadd(day,1,@toDate)MadhivananFailing to plan is Planning to fail |
 |
|
|
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) ?? |
 |
|
|
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 MadhivananFailing to plan is Planning to fail |
 |
|
|
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. |
 |
|
|
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 MadhivananFailing to plan is Planning to fail |
 |
|
|
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. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-25 : 05:39:59
|
Its a useful little trick |
 |
|
|
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. |
 |
|
|
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]) + ',%' |
 |
|
|
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 etcMadhivananFailing to plan is Planning to fail |
 |
|
|
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. |
 |
|
|
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! |
 |
|
|
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. |
 |
|
|
|
|
|
|
|