SQL Server Performance Forum – Threads Archive

in line table function

Hi I’m new here. I need to go through all my stored procedures and exclude certain records from all tables that reference the salesid. I created an inline table function to do this: set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ExcludeFab]
(@Salesid varchar(25))
RETURNS TABLE
AS
RETURN SELECT *
from SalesTable
where SalesTable.salespoolid != ‘fab’
and salesid = @salesid This works fine if I pass the salesid as a string (ie ‘1111’) but I cannot pass the salesid itself to this function. If I attempt this I get: Msg 155, Level 15, State 1, Line 2
‘salesid’ is not a recognized OPTIMIZER LOCK HINTS option. Can anyone help?
Thanks in advance bbrian

table function can only accept constant as input
KH

you need to do this in a stored procedure not in a function —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Go to<a target="_blank" href=http://www.sommarskog.se>http://www.sommarskog.se</a> and read Erlands’ article about array and lists. And while you’re there, read also the article about dynamic SQL. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

why don’t you just perform a (left)join ?

]]>