Need help with SQL 2000 Stored Procedure

Question

This is my logic.

I have an articles table and a matching images tables. The images are stored in binary format. Each image table has 2 instances of each image, this is because I have 2 sizes. 300 x 200 and 500 x 400 with their rows separated by ImageSize

I want to write a stored procedure that checks if ImageSize=3 is available and if not, then retrieve from ImageSize=2.

How can I set up my stored procedure to do this?

Thanks

Answer 1

You can find the highest size image with a simple top 1:

create procedure dbo.GetImage(
    @ImageName varchar(50))
as
select  top 1 image
from    ImageTable
where   ImageName = @ImageName
order by
        ImageSize desc

Answer 2

Andomar's response is completely valid and very elegant - if you want something more "down to earth", that takes into account there might be other number values involved in the future, try this:

CREATE PROCEDURE dbo.FetchImage(@ImageName VARCHAR(255))
AS
   IF EXISTS(SELECT * FROM dbo.ImageTable
             WHERE ImageName = @ImageName AND ImageSize = 3)
      SELECT
          Image
      FROM
          dbo.ImageTable
      WHERE
          ImageName = @ImageName AND ImageSize = 3
   ELSE
      SELECT
          Image
      FROM
          dbo.ImageTable
      WHERE
          ImageName = @ImageName AND ImageSize = 2

That basically does the same thing - but it'll continue to return the Image for ImageSize = 3 even if you suddenly also have image sizes codes of 4, 5, and so forth.