Try again with the variable number of rows. Here's a script to run in tempdb (so it won't screw anything up). After running the whole script, rerun the final query...

pietlinden (1/8/2014)

---

Thanks for the ideas, everybody.

I got it to work, which is good, but I had to resort to <whisper>a dreaded cursor.</whisper> I know, it's a total cheat, but...

quindraco (1/8/2014)

---

Thank you! You've finally sussed it. I went back to my function and modified it to rtrim the select from the table with the mangled data type...

Is [address] the only filter/parameter you are intending to pass in?

If you are still wondering why the last query in your script returns NULL for the fullfilename (or whatever), it's because you've been inconsistent with your datatypes.

-- Columns BOOK and...

Sometimes 😀

Your sample code generates errors:

Msg 208, Level 16, State 1, Procedure imageFileNameFromAddress, Line 5

Invalid object name 'dbo.imageFileFromBookPage'.

(1 row(s) affected)

(3 row(s) affected)

Msg 208, Level 16, State 1, Line 3

Invalid object name...

The whole of your output (everything in the SELECT list) is unrelated to anything else in your query. It looks like a bunch of columns but the syntax is incorrect....

Here's a proposal which reads the source table only once (you didn't see that table spool)

SELECT

[User ID], [Group ID], [Company]

FROM (

SELECT

[User ID], [Group ID] = MAX([Group ID]), [Company],...

Jeff is proposing...

elham_azizi_62 (1/8/2014)

---

my favorit out put is:

rownumber main2.linknumber main2.savabegh main1.linknumber

1 29027 ...

Good, thanks for the feedback.

I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but it...

elham_azizi_62 (1/8/2014)

---

... I want to apply another column that exist in another table with name main1,in main1 'name' filed equall with 'savabegh' filed in main2:

main1:linknumber(int),name(varchar)

main2:linknumber(int),savabegh(varchar)

I want to have...

As Gail has already pointed out, you're unnecessarily overcomplicating your code. Your stored procedure is the equivalent to this:

ALTER procedure...