In line function with table name as parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

In line function with table name as parameter

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2002-05-27 : 04:07:20

I have tables that are used in multiple locations. In order to split the information, I have added in those tables a field IDX_NB tinyint that will filter it.

To get a table, the following procedure is used:

Alter procedure usp_A_Table (@MyTable as nvarchar(64))
/*returns all the record from a table for the current user, current location*/

as
declare @T as NVARCHAR(500)
SET NOCOUNT ON
set @t='Select * from '+@mytable
set @t=@t +' where '+@mytable+'.IDX_NB=dbo.usf_A_IDX_Table('
set @t=@t +char(39)+@mytable+char(39)+')'

execute sp_executesql @t

I would like to have it in an in-line function, but I don't manage to do it.

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-05-28 : 07:40:22

Are you talking about a UDF (user defined function)? That was first introduced in MS SQL server 2000. It would be quite easy to create a function as mentioned, but I always create a view on top of sysobjects and link any queries to that view.

Maybe you should tell us what you want to use it for.

Mathias
Posting Yak Master

119 Posts

Posted - 2002-05-29 : 09:53:05

The goal is to use one UDF to view the table. It is easy to define it by naming the fields for a specific table.
What I was trying to avoid is the creation of one UDF per table.

create function SeeTable_PERSON_TBL
Returns table as
return (select NAME from PERSON_TBL where PERSON_TBL.IDX_NB=1)

What I am looking for is something like :
create function SeeTable(@TableName as varchar(50))
Returns table as
return (select * from @TableName where @TableName.IDX_NB=1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources