Table variable with Image datatype question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table variable with Image datatype question

Author

Topic

JoeSurls
Starting Member

2 Posts

Posted - 2009-04-15 : 21:29:00

I have a table with two rows in it. One column on this table holds a logo image (image datatype). I need a way to "flatten" out this table so that it presents as:

Column1 Column 2
======= ========
Logo1 Logo2

So that the two image columns are presented as one row in the result. this is for displayiing said logos in a report. I have tried Cross join, Union, et al. but it still puts one logo on one records and logo two on a second record.
Currently, I am trying a table-valued function, but having an issue inserting the second logo into the table-valued variable.
Here is the function(does not work):

ALTER FUNCTION dbo.udf_logo()

RETURNS @ret_table TABLE (LOGO1 VARCHAR(4000), LOGO2 VARCHAR(4000))

AS

BEGIN

INSERT INTO @ret_table(LOGO1)
SELECT LOGO_VARIABLE_NAME FROM LOGO
WHERE LOGO_VARIABLE_NAME = '%COMPANY_LOGO1%'

UPDATE @ret_table
SET LOGO2 = (
SELECT LOGO_VARIABLE_NAME FROM LOGO
WHERE LOGO_VARIABLE_NAME = '%COMPANY_LOGO2%')

RETURN

END

Also, UPDATETEXT will not work with table valued variables either. Does anyone have any suggestions as to how to accomplish this?

Ideally, I should be able to have a data set and be able to cross join to this function so that I get two logo(image) fields for each record in the other table. For example (with three records from teh other table):

Tabl1Col1, Tabl1Col2, Tabl1Col3, LOGO1, LOGO1
Tabl1Col1, Tabl1Col2, Tabl1Col3, LOGO1, LOGO1
Tabl1Col1, Tabl1Col2, Tabl1Col3, LOGO1, LOGO1

I hope I am being clear with this. I can try to explain further if necessary. Thanks for any help or suggestion.

Joe

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 10:33:00

Try something like this

ALTER FUNCTION dbo.udf_logo()
RETURNS TABLE
AS
BEGIN

SELECT	(
		SELECT LOGO_VARIABLE_NAME FROM LOGO
		WHERE LOGO_VARIABLE_NAME = '%COMPANY_LOGO1%'
	),
	(
		SELECT LOGO_VARIABLE_NAME FROM LOGO
		WHERE LOGO_VARIABLE_NAME = '%COMPANY_LOGO2%'
	)

	RETURN
END

E 12°55'05.63"
N 56°04'39.26"

JoeSurls
Starting Member

2 Posts

Posted - 2009-04-20 : 11:51:51

Thanks for your reply to my issue! That does work perfectly but not with an image datatype. I should have been clearer in my original post. I substituted a varchar(4000) in place of my image type in my testing and it got posted in my original post.

When I run this against my image datatype, I get:
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Is there a way to do this with an image datatype?

Thanks again!
Joe

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 12:09:01

You can probably use CAST in the two subselects.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources