The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
I created a procedure (using SQL Server 2008) to retrieve th开发者_如何学编程e image data from image
table but this procedures giving me an error
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
My procedure is this:
Create procedure [dbo].[xp_GetImage]
@companyId udtId
as
begin
/*=============================================================================
* Constants
*============================================================================*/
declare
@SUCCESS smallint,
@FAILED smallint,
@ERROR_SEVERITY smallint,
@ERROR_STATE1 smallint,
@theErrorMsg nvarchar(4000),
@theErrorState int,
@chartCount int,
@provider varchar(128),
@projectCount int
select
@SUCCESS = 0,
@FAILED = -1,
@ERROR_SEVERITY = 11,
@ERROR_STATE1 = 1
begin try
-- Get the Image
select Logo, LogoName,LogoSize
from CompanyLogo
where CompanyId = @companyId
order by Logo desc
end try
begin catch
set @theErrorMsg = error_message()
set @theErrorState = error_state()
raiserror (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)
return (@FAILED)
end catch
end
print 'created the procedure xp_GetImage'
go
---end of the procedure
grant EXECUTE on xp_GetImage to public
go
please help me.
Don't forget about CAST(). It just got me out of trouble looking for a string in a text field, viz
SELECT lutUrl WHERE CAST(Url AS varchar) = 'http://www.google.com.au'
The blurb that helped me is at Mind Chronicles. The author discusses the sorting issue as well.
It doesn't make sense to sort (order) by binary image data. Why don't you sort by one of the other columns instead?
Example
Modify the code from this:
-- Get the Image
SELECT Logo, LogoName,LogoSize
FROM CompanyLogo
WHERE CompanyId = @companyId
ORDER BY Logo desc
To this:
-- Get the Image
SELECT Logo, LogoName,LogoSize
FROM CompanyLogo
WHERE CompanyId = @companyId
ORDER BY LogoName
精彩评论