How to get a SQL UDF to return image data type???

[Guest]

Explanation:
We have several SP that need to retrieve a single "Default Photo" from one
of several Photo tables. The column in question in these tables is defined as
an IMAGE data type. I currently have a SP that that return the default photo
and other relative information.

Several of my developers want to retrieve only the image column from this
SP. So I thought Id create a function that would execute code very similar to
the SP version and only return the image data. This way they could use the
function inside of a select statement without the need to inner join all the
needed tables.

Issue:
Problem is that SQL function does not allow a return type of IMAGE (don’t
know why, love to hear a reason on that one). So ok, I thought, lets return
the image as BINARY and do a CONVERT(image, dbo.MyBinaryData()). These
function executes but any result comes back as 0xFF or NULL. Id expect NULL
if no default photo was ever set, so that’s cool, it’s the ones where it
should be returning the entire image that’s the problem

How can I get Image data from a UDF?

 

[AlexS]

The easiest way is to forget about UDF and just to select that one field

I am not sure what your developers mean though. They can get result set from
your sp and use just this field (column) ignoring the rest if any
additional columns are selected. What is making them unhappy?

 

[Guest]

Got it working with UDF. Basically:

DECLARE @image as VARBINARY(max)
SELECT @image = CONVERT(VARBINARY,imagePhoto) FROM table
RETURN @image



--
JP
..NET Software Developer

The easiest way is to forget about UDF and just to select that one field

I am not sure what your developers mean though. They can get result set from
your sp and use just this field (column) ignoring the rest if any
additional columns are selected. What is making them unhappy?

 

[sloan]

Yeah , I was going to say look at this:

http://novicksoftware.com/UDFofWeek...1-Number-12-udf_SQL_VariantToDatatypeName.htm

Got it working with UDF. Basically:

DECLARE @image as VARBINARY(max)
SELECT @image = CONVERT(VARBINARY,imagePhoto) FROM table
RETURN @image