join syntax Multi-statement table valued function

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 2008 Forums

Other SQL Server 2008 Topics

join syntax Multi-statement table valued function

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-29 : 13:49:16

Hello all. I have a syntax problem that I can't seem to figure out.

I have a multi-statement valued table function that accepts 3 parameters. A guid for an image file, a maximum height parameter and a maximum width parameter. I need to join on the function to get the resized height and width for the image. I have not worked with a table function with more than one parameter before.

Can you tell me what I am doing wrong? Here is part of the body of the stored procedure:

SELECT *
FROM CHILD C
INNER JOIN dbo.CHILD_CASE CC ON CC.CHILD_GUID = C.CHILD_GUID 
INNER JOIN dbo.CHILD_IMAGE CI ON CI.CHILD_CASE_GUID = CC.CHILD_CASE_GUID
INNER JOIN dbo.SITE_IMAGE SI ON SI.SITE_IMAGE_GUID = CI.SITE_IMAGE_GUID 
INNER JOIN dbo.fun_ResizeImage_asTable(SI.SITE_IMAGE_GUID, 300, 250) IR
 	
WHERE CHILD_PLACED = 0

Here is part of the code for the function:

CREATE FUNCTION [dbo].[fun_ResizeImage_asTable] 
(	@ImageGuid varchar(36),
	@intImageMaxHeight int,
	@intImageMaxWidth int
)  

RETURNS @Results  TABLE	(
					ImageHeight int,
					ImageWidth int
					)
AS  

	BEGIN 
		
...

		RETURN 
	END

When I try to compile it I get an error "Incorrect syntax near '.'." My function works great, so I didn't include code for it. The stored proc above is what is giving me problems.

Thanks!

Aj

Hey, it compiles.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 13:55:01

Where is the ON condition for the JOIN to the function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 13:56:01

it should be CROSS APPLY rather than INNER JOIN


SELECT *
FROM CHILD C
INNER JOIN dbo.CHILD_CASE CC ON CC.CHILD_GUID = C.CHILD_GUID 
INNER JOIN dbo.CHILD_IMAGE CI ON CI.CHILD_CASE_GUID = CC.CHILD_CASE_GUID
INNER JOIN dbo.SITE_IMAGE SI ON SI.SITE_IMAGE_GUID = CI.SITE_IMAGE_GUID 
CROSS APPLY dbo.fun_ResizeImage_asTable(SI.SITE_IMAGE_GUID, 300, 250) IR 	
WHERE CHILD_PLACED = 0

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-29 : 14:45:55

visakh16, same error when I change to a cross apply.

Tara, if I inner join, what would I use as my ON statement? The function will return the two values I need. The function is really only dependent on the SI.SITE_IMAGE_GUID parameter. The other two, while required, are only there so I can get fancy in the future and specify the max height and width dynamically.

Thanks!

Hey, it compiles.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:49:24

quote:
Originally posted by ajthepoolman

visakh16, same error when I change to a cross apply.

Tara, if I inner join, what would I use as my ON statement? The function will return the two values I need. The function is really only dependent on the SI.SITE_IMAGE_GUID parameter. The other two, while required, are only there so I can get fancy in the future and specify the max height and width dynamically.

Thanks!

Hey, it compiles.

whats the sql server version you're using? also whats the client tool you're using to run the query?

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-29 : 16:41:25

This is sql 2008. I am just using the Microsoft SQL Server Management Studio.

EDIT: I just realized that the compatibility level is set to 80 for this database. So it is running as a 2000 database. I suppose that would make a slight difference!

I will talk to my DBA and see if we can bump it up (it is a development database for now.).

Sorry to waste your time on this!

Hey, it compiles.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:44:56

No problem. It should work fine once you change compatibility level to 90 or 100. use ALTER DATABASE SET COMPATIBILITY LEVEL... for changing compatibility level

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 01:06:04

Do not switch the compatibility level unless you plan on doing thorough testing of it. At the very least you need to run the upgrade advisor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources