Comparing text in WHERE

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@.Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@.Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

hi,

text/ntext datatype does not support this kind of operation/comparison... you can find the "available" methods against these datatypes in http://msdn2.microsoft.com/en-us/library/ms187993.aspx..

these datatype are not good candidates for "filtering" operations as well.. consider that text can hold up to 2gb of data, and it's not worth the problem to support such heavy features..

so you can end up with "workarounds" if they fit your needs, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Value text NULL ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 'some text' ); INSERT INTO dbo.TestTB VALUES ( 2 , 'some other text' ); GO DECLARE @.key varchar(10); SET @.key = 'some text'; SELECT * FROM dbo.TestTB WHERE SUBSTRING( Value, 1, DATALENGTH(@.key)) = @.key; GO DROP TABLE dbo.TestTB; --<- Id Value -- 1 some text

but consider the other methods as well..

if you are using SQL Server 2005, consider moving the text/ntext datatypes to varchar(MAX)/nvarchar(MAX), as text datatype has been deprecated and, more usefull, varchar(MAX) supports all the traditional "string" operations...

regards

sqlsql