How to implement OUTER APPLY and table valued function in SQL 2000

Question

3.40/5 (3 votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hi,

I created a SQL store procedure in SQL 2005 compatibility level 90.
But when I deployed the same code we found on server comatibility level is 80. Now we cant change comtability level to 90 on Prod server.

I am using below code:

SQL

select Results.value from ##NameSplitedOnSpace as tbl outer apply dbo.fn_SplitString(tbl.value,'-') as Results

Above code works fine in SQL server 2005 with compatibility 90 but not in comatibility level 80 as SQL 2000 doesnt support Cross apply

Please help me to get the above code which support level 80.

Thanks in advance.

Posted 16-Feb-14 1:39am

Singh Gyan

Add a Solution

Comments

Andrius Leonavicius 16-Feb-14 10:16am

Hi,

Just to make things clear. Are you going to use this code with SQL Server 2000 or is it only SQL Server 2005 with compatibility level 80?

Andrius Leonavicius 16-Feb-14 10:25am

I'm asking this because I know that APPLY can work in compatibility level 80 with SQL Server 2005 or greater (it might not work in all situations, not sure about yours)...

[no name] 20-Feb-14 7:19am

It is only SQL Server 2005 with comatibility level 80.

Andrius Leonavicius 20-Feb-14 12:25pm

So have you tried to run your procedure with compatibility level 80?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

skydger · Answer 1 · 2014-02-16T22:37:00

Hello. There is no way to join table valued functions with non-static argument in MS SQL 2000.
For instance the code with static string will work

SQL

SELECT Results.value
  FROM ##NameSplitedOnSpace AS tbl
  JOIN dbo.fn_SplitString('my_sample', '-') AS Results
    ON Results.MyColumn = tbl.MyOtherColumn

While the code with specified table's column will not.
Consider to use cursor for this purpose. This might have a bigger code but you'll maintain compatibility.

SQL

DECLARE @val AS varchar (255)
DECLARE @v_table TABLE ( /*here goes your ##NameSplitedOnSpace table structure or part of it */ )

DECLARE vals CURSOR FOR
 SELECT tbl.idx
   FROM ##NameSplitedOnSpace as tbl

 OPEN vals
FETCH NEXT FROM vals INTO @val
  
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO @v_table
  SELECT * --or use specific columns for correlation with @v_table table
    FROM dbo.fn_SplitString(@val, '-')
   FETCH NEXT FROM vals INTO @val
END

CLOSE vals
DEALLOCATE vals

SELECT * FROM @v_table

Hope it would be helpful.
Good luck :)