CODEWITHSUNDEEP
sqlsql-servert-sql
user1027620
user1027620

Reputation: 2785

SQL Server 2008 column prefix error

So I have this working properly:

CREATE FUNCTION dbo.GetLiveStream(@UserName NVARCHAR(MAX)) RETURNS TABLE
AS
  RETURN (
    SELECT DISTINCT l.* 
      FROM StreamView l
      JOIN Friendships f ON f.Sender = @UserName OR f.Recipient = @UserName 
     WHERE l.Sender <> @UserName 
       AND l.Recipient <> @UserName 
       AND (   l.Sender = f.Recipient 
            OR l.Sender = f.Sender 
            OR l.Recipient = f.Sender 
            OR l.Recipient = f.Recipient) 

  )
GO

But when trying to do the following query:

SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS RowNumber, 
       * 
  FROM dbo.GetLiveStream('jason') 
  WHERE RowNumber >= 0
  AND RowNumber < 0+ 10;

I get the following error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'RowNumber'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'RowNumber'.

Note that specifying l.* returns this error:

Msg 107, Level 15, State 1, Line 2
The column prefix 'l' does not match with a table name or alias name used in the query.

GetLiveStream is a function:

RETURN (
    SELECT DISTINCT l.* 
      FROM StreamView l
      JOIN Friendships f ON f.Sender = @UserName OR f.Recipient = @UserName 
     WHERE l.Sender <> @UserName 
       AND l.Recipient <> @UserName 
       AND (   l.Sender = f.Recipient 
            OR l.Sender = f.Sender 
            OR l.Recipient = f.Sender 
            OR l.Recipient = f.Recipient) AND f.IsPending = 0

  )

How can this be resolved? Thank you.

Upvotes: 3

Views: 1070

Answers (2)

John Pick
John Pick

Reputation: 5650

Don't use * in a stored procedure or function because it gets compiled into a list of columns, which can change over time and thus break the compiled code. SQL Server doesn't warn you when that happens.

Upvotes: -1

user596075
user596075

Reputation:

You can't reference an alias in the WHERE clause. Do this:

select *
from
(
    SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS RowNumber, 
           * 
      FROM dbo.GetLiveStream('jason') 
) a
  WHERE RowNumber >= 0
  AND RowNumber < 0+ 10;

As for your l.* error, that's because that alias is local to the function and its query definition and can't be referenced outside of it.

Upvotes: 4

Related Questions