"Code" = "code" in Store Procedure

"Code" = "code" in Store Procedure

Post by TNL » Wed, 06 Jun 2001 05:35:29



Halle Everybody,

in a Store Procedure I use a select Statement as following:


the Variable p_Password had value "code", in the table there is only a
record with the value "Code".
The Select statement return me a record, that means SQL Server doesn't make
different between "code" and "Code".

Where is the problem? Where can I change the setting?

Thanks


 
 
 

"Code" = "code" in Store Procedure

Post by Aaron Bertran » Wed, 06 Jun 2001 05:49:17


You can use the COLLATE hint, compar the results of the three columns in the
following code:

SELECT
    x = CASE
        WHEN 'Case'='case' COLLATE latin1_general_cs_as
        THEN 1
        ELSE 2
    END,
    y = CASE
        WHEN 'Case'='Case' COLLATE latin1_general_cs_as
        THEN 1
        ELSE 2
    END,
    z = CASE
        WHEN 'Case'='case'
        THEN 1
        ELSE 2
    END

--
   MVP [ASP]
   http://www.aspfaq.com/


> Halle Everybody,

> in a Store Procedure I use a select Statement as following:


> the Variable p_Password had value "code", in the table there is only a
> record with the value "Code".
> The Select statement return me a record, that means SQL Server doesn't
make
> different between "code" and "Code".

> Where is the problem? Where can I change the setting?

> Thanks




 
 
 

"Code" = "code" in Store Procedure

Post by Darren Gree » Wed, 06 Jun 2001 06:28:58



>Halle Everybody,

>in a Store Procedure I use a select Statement as following:


>the Variable p_Password had value "code", in the table there is only a
>record with the value "Code".
>The Select statement return me a record, that means SQL Server doesn't make
>different between "code" and "Code".

>Where is the problem? Where can I change the setting?

>Thanks



This is due to the sort order you set when installing SQL Server, case-
insensitive.

Aaron's COLLATE solution is SQL 2000 only. For SQL 7.0 you can CONVERT
the two values to varbinary and the compare. Make sure you include the
current where clause as well otherwise it will ignore any index on
[Password] if using the converted values only.

--
Darren Green
http://www.sqldts.com

 
 
 

"Code" = "code" in Store Procedure

Post by David Molone » Wed, 06 Jun 2001 07:24:26


Had the same problem TNL and found that Darren's response is spot on.

Here is abit of code to demonstrate


as
set nocount on
Select * from LOGIN

varbinary(50))

HTH

DavidM


> Halle Everybody,

> in a Store Procedure I use a select Statement as following:


> the Variable p_Password had value "code", in the table there is only a
> record with the value "Code".
> The Select statement return me a record, that means SQL Server doesn't
make
> different between "code" and "Code".

> Where is the problem? Where can I change the setting?

> Thanks



 
 
 

"Code" = "code" in Store Procedure

Post by Tibor Karasz » Wed, 06 Jun 2001 16:27:36


TNL,

You already got some answer for this. Below is just some added info and some examples:

In SQL Server 2000, you have default collation at server level, database level, table
level. All can be overridden, down to column level. Also, you can override the
collation for data in a query using the COLLATE clause.

In SQL Server 7, you can get case sensitive comparison in query by converting to
binary. Below should explain:

Use Northwind

--Create copy of Orders table
SELECT * Into #Orders FROM Orders

--Create Index on ShipName
CREATE INDEX o_sh ON #Orders(ShipName)

--Make sure that we get one Orders.ShipName
--which only differs in case
UPDATE #Orders
SET ShipName = 'save-a-lot markets'
WHERE OrderID = 10627

--Convert string to binary:
SELECT OrderID, ShipName
 FROM #Orders
 WHERE CAST(ShipName AS BINARY(80))
     = CAST(N'save-a-lot markets' AS BINARY(80))

--Enable usage of index
SELECT OrderID, ShipName
 FROM #Orders
 WHERE CAST(ShipName AS BINARY(80))
     = CAST(N'save-a-lot markets' AS BINARY(80))
 AND ShipName = N'save-a-lot markets'

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


> Halle Everybody,

> in a Store Procedure I use a select Statement as following:


> the Variable p_Password had value "code", in the table there is only a
> record with the value "Code".
> The Select statement return me a record, that means SQL Server doesn't make
> different between "code" and "Code".

> Where is the problem? Where can I change the setting?

> Thanks



 
 
 

"Code" = "code" in Store Procedure

Post by TNL » Wed, 06 Jun 2001 16:42:32


Hi,

Thanks for your answers. I will try it.

TNL

-----Original Message-----

Halle Everybody,

in a Store Procedure I use a select Statement as following:


the Variable p_Password had value "code", in the table there is only a
record with the value "Code".
The Select statement return me a record, that means SQL Server doesn't make
different between "code" and "Code".

Where is the problem? Where can I change the setting?

Thanks


.

 
 
 

"Code" = "code" in Store Procedure

Post by Aaron Bertran » Wed, 06 Jun 2001 22:53:38


Quote:> Aaron's COLLATE solution is SQL 2000 only.

Yes, sorry, I should have qualified that.  Though, in my defense, it would
be nice if people stated which version they use!  :-)

--
   MVP [ASP]
   http://www.aspfaq.com/