how to pass table name as a parameter in UD scaller function?

create function test
(
  @tblName as  sysname,
  @category as varchar(50)
)
returns int
as
begin 
  declare @val2 as int
  se开发者_高级运维lect @val2=val1 from @tblName where val1=@tblName 
  return @val2
end

Whats wrong with this query, it's throwing the following error

Msg 1087, Level 15, State 2, Procedure test, Line 11

Must declare the table variable "@tblName".

You can't parameterise the table name. You would need to use dynamic SQL (not that this is allowed in functions anyway).

You might be able to use a View or CTE that UNION ALLs a fixed list of tables and a constant to do whatever you are trying to do. The code you have posted makes little sense (You want @val2 to have the same value as @tblName if a table called the value of @tblName contains this same value in the val1 column?!) but something like

;WITH cte AS
(
SELECT 'table1' AS table_name, val1
FROM table1
UNION ALL
SELECT 'table2' AS table_name, val1
FROM table2
)
select @val2=val1 from cte where table_name=@tblName and val1 = @tblName

@tblName1 is decalred as @tblName in the function parameter