开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜