Playing with cross apply

Question

Post reply

Playing with cross apply

sirishgreen

Old Hand

Points: 398
More actions
October 18, 2013 at 6:39 am

#278189

Hi there,
This is my first post so hello everyone.
I am trying to get my head around cross apply and have written the below:
-- my function
CREATE FUNCTION [dbo].[getUPRN]
(@CPD_UPRN_ID AS varchar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT CPD.CPD_UPRN_ID,CPD.CPD_UPRN FROM dbo.Core_Property_Details AS CPD
WHERE CPD.CPD_UPRN_ID = @CPD_UPRN_ID
-- Call using cross apply
SELECT P.cpd_uprn_id, fuc.CPD_UPRN
FROM core_property_details AS P
CROSS apply [dbo].[getUPRN](p.cpd_uprn_id) AS fuc
-- ERROR MESSAGE
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
---------------------------------------------------------------------------
I know I'm doing something stupid bud don't know what.
Please help and thanks in advance.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

Do you execute both statements at the same time?

If yes, try creating the function first and then execute the statement with cross apply.

I took your code and modified it to use two tables in my database, and it worked.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sirishgreen Old Hand Points: 398 More actions · Answer 2

Thanks for the quick response.

Unfortunately I am already running them separately.

weird.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

sirishgreen (10/18/2013)
Thanks for the quick response.
Unfortunately I am already running them separately.
weird.

Weird indeed.

Can you post table DDL and some sample data, so that I can replicate the exact same set-up?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sirishgreen Old Hand Points: 398 More actions · Answer 4

I have replicated the problem using the below table:

CREATE TABLE [dbo].[Core_Property_Details](

[CPD_UPRN_ID] [nvarchar](20) NOT NULL,

[CPD_UPRN] [nvarchar](20) NULL,

CONSTRAINT [PK_Core_Property_Details1] PRIMARY KEY CLUSTERED

(

[CPD_UPRN_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

The function, and function call, I posted earlier should work with the above.

You can enter a record with any old thing in there.

Thanks

again.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 5

Created your table, your function and executed the query without a problem.

Which version of SQL Server are you using?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sirishgreen Old Hand Points: 398 More actions · Answer 6

Well I'm please there is nothing wrong with my function\query 🙂

10.50.4000.0 2008R2 DEV

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

I don't see anything wrong here either. I created your table and function and received no error.

If you want to learn about APPLY you might want to look at this 2 part series from Paul White.

http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D

http://www.sqlservercentral.com/articles/APPLY/69954/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sirishgreen Old Hand Points: 398 More actions · Answer 8

sirishgreen

Old Hand

Points: 398

October 18, 2013 at 8:58 am

#1659748

Thanks. They are what I'm using 🙂

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

Did you check the database compatibility level?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sirishgreen Old Hand Points: 398 More actions · Answer 10

Alas that was my problem - Thank you everyone for your help.

My database compatability level was set to (80) after setting it to 100 everything worked as expected.

thanks mister.magoo.