November 25, 2009 at 4:55 am
DECLARE @sql AS VARCHAR(MAX)
SET @sql = 'SELECT SRequest.SRequestId FROM dbo.vw_SRequest SRequest
LEFT JOIN vw_LinkSRequestNameAddress LinkSRequestNameAddress
.
.
.
+ @Whereclause
+ ' GROUP BY SRequest.SRequestId'
The WHERE clause is passed in as a parameter after being dynamically built.
It has been noted that the selection includes amongst many others a post code / zip code that is being selected case sensitively. There are other parts of the WHERE clause that will contain fields where the sensitivity may or may not matter depending on the users choice of selection criteria.
1. How might I change the collation at the WHERE clause?
(NB. We do not want to go down the route of changing the database collation - there seems to be limited info out there on the interwebz re. best practise and I have found no definitive answer).
2. Can i set the COLLATE instruction to cover all sensitive elements of the clause?
3. Can I set it in the WHERE clause inside the SP?
3. Should I build it as part of the dynamically built parameter that is the @Whereclause?
Any advice appreciated.
Many thanks.
November 25, 2009 at 5:49 am
mark.veitch (11/25/2009)
DECLARE @sql AS VARCHAR(MAX)SET @sql = 'SELECT SRequest.SRequestId FROM dbo.vw_SRequest SRequest
LEFT JOIN vw_LinkSRequestNameAddress LinkSRequestNameAddress
.
.
.
+ @Whereclause
+ ' GROUP BY SRequest.SRequestId'
The WHERE clause is passed in as a parameter after being dynamically built.
It has been noted that the selection includes amongst many others a post code / zip code that is being selected case sensitively. There are other parts of the WHERE clause that will contain fields where the sensitivity may or may not matter depending on the users choice of selection criteria.
1. How might I change the collation at the WHERE clause?
(NB. We do not want to go down the route of changing the database collation - there seems to be limited info out there on the interwebz re. best practise and I have found no definitive answer).
2. Can i set the COLLATE instruction to cover all sensitive elements of the clause?
3. Can I set it in the WHERE clause inside the SP?
3. Should I build it as part of the dynamically built parameter that is the @Whereclause?
Any advice appreciated.
Many thanks.
Hi,
This is how you can change the collation in your WHERE clause
SELECT Sample
FROM (
SELECT 'Some sample text' AS Sample
) AS A
WHERE Sample COLLATE LATIN1_GENERAL_CS_AS = 'some sample text'
This example changes the collation to be case sensitive (CS) from my default of case in-sensitive.
Hope this helps you out.
November 25, 2009 at 5:58 am
Hi,
Thanks for that. I should have said that I had decide to use that Collation. My problem is whether or not it will fit into my SP given that the Whereclause is dynamically built and may or may not contain several x = y selections which may or may not be case dependant.
Can I use one COLLATE to cover all?
November 25, 2009 at 6:06 am
No, to my knowledge your going to need to set the COLLATE for each of your statements, I'm not aware of a COLLATE command that would cover all of them.
Sorry.
November 25, 2009 at 6:09 am
Bleurgh!!!!
Ok, thanks very much for your help.
Might try to add a COLLATE for each relevant column where we build our WHERE string.
Could be messy. :pinch:
November 25, 2009 at 6:15 am
yeah it is going to get a little messy. :w00t:
I'm just wondering is there any other solution, the issue is that your needing to compare different collations!
so can the collations be standardised? For example if this is a reporting solution then the data could be loaded into a new db which has its collation set, then all the data is on the same db with a specific collation. I know this example is probably way off what your up but just throwing some idea's around.
Can I ask why you have different collations being used?
Thanks,
November 25, 2009 at 6:24 am
Well it's not that there are different COLLATEs for different columns; it's more that when the users run a SP there may be case sensitive fields that they are searching on, or there maybe just non-CS fields or there may be a mixture of data types.
My understanding from what has been said above is that I cannot wrap my WHERE statement in one cover-all COLLATE... I need to apply the same COLLATE to each individual cash sensitive field that is included in the WHERE.
(Yep, it's complicated why it's all been set up this way but it's too late for a high level rethink).
November 25, 2009 at 6:31 am
As far as i'm aware on the WHERE clause you cant apply a collation to all, it has to be on each one.
However, you can set the collation for a specific column in a table, some maybe thats a method we can exploit???
Is it a case of your column is always going to be case insensitive and certain calls to the procedure may require it to operate in a case sensitive manner?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply