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

[=?Utf-8?B?SlA=?=]

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.MyBinaryDat a()). 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?

--
JP
..NET Software Developer

[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?
"JP" <JP@discussions .microsoft.comw rote in message
news:08******** *************** ***********@mic rosoft.com...

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.MyBinaryDat a()). 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?

--
JP
.NET Software Developer

[=?Utf-8?B?SlA=?=]

Got it working with UDF. Basically:

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

--
JP
..NET Software Developer
"AlexS" wrote:

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?
"JP" <JP@discussions .microsoft.comw rote in message
news:08******** *************** ***********@mic rosoft.com...

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.MyBinaryDat a()). 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?

--
JP
.NET Software Developer

[sloan]

Yeah , I was going to say look at this:

http://novicksoftware.com/UDFofWeek/...tatypeName.htm

"JP" <JP@discussions .microsoft.comw rote in message
news:B3******** *************** ***********@mic rosoft.com...

Got it working with UDF. Basically:

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

--
JP
.NET Software Developer
"AlexS" wrote:

>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?
"JP" <JP@discussions .microsoft.comw rote in message
news:08******* *************** ************@mi crosoft.com...

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â?Tt
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.MyBinaryDat a()). These
function executes but any result comes back as 0xFF or NULL. Id expect
NULL
if no default photo was ever set, so thatâ?Ts cool, itâ?Ts the ones
where
it
should be returning the entire image thatâ?Ts the problem

How can I get Image data from a UDF?

--
JP
.NET Software Developer