SQL Function Creation Error

You cannot treat a Varchar as a Table.

@Table_Name is just a varchar holding name of the table, it isnt the actual table you can query. If you want to use @Table_Name to query actual table you need to use Dynamic SQL. Something like below:

CREATE FUNCTION TBN
(       
        @RequestTypeID int,
        @RequestSerialNo int
)
RETURNS TABLE 
 
AS
BEGIN
        DECLARE @TABLE_NAME varchar(50)
        set @TABLE_NAME = (select Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID)
        
        DECLARE @SQL VARCHAR(200)
 
        SET @SQL = 'select * from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
 
        Return EXECUTE (@SQL)
 
END
GO

Select allOpen in new window

you have to define the structure of return table, have a look at my small article at.

http://www.sqlhub.com/2009/03/multi-statement-table-valued-function.html

Since the return table needs to be defined (you have to know what the columns will be), and I assume that the different table names that you intend to populate the @TABLE_NAME variable with will presumably have different structures, I don't think you will find your solution in such a function.  

Here is an example of working function syntax, with some notes:
create table ABC (
id      int not null,
abc char(3) not null)

set nocount on;
insert ABC (id, abc) values (1, 'xyz')

CREATE FUNCTION dbo.TBN (@RequestTypeID int, @RequestSerialNo int)
RETURNS @ABC TABLE (id int, abc char(3))
AS
begin

-- declare and set table variable name (just showing that you can declare the variable here
DECLARE @TABLE_NAME varchar(50); select @TABLE_NAME = Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID

-- insert the return table.  cannot use dynamic sql to insert the @ABC return table
insert @ABC (id, abc) select id, abc from ABC where 1=1

-- the return statement goes here
RETURN  
end
GO

If the structure of your tables happens to be the same, you should be able to use if statements in the function to select the data you want into your return table.

Like this:

if (@TABLE_NAME = 'ABC')
begin
  insert @ABC (id, abc)
  select * from ABC where Request_Serial_No=@RequestSerialNo
end

HTH

As a combination of [expertsoul]'s and [rrjegan17]'s comment, I have used this code, but it still gives me the following error:
Msg 170, Level 15, State 31, Procedure TBN, Line 19
Line 19: Incorrect syntax near 'BEGIN'.
 
GSQL & RiteshShah:
This is if you know which table you're trying to return data from, but in my case, the table name is dynamically determined at run time.

CREATE FUNCTION TBN
(       
        @RequestTypeID int,
        @RequestSerialNo int
)
RETURNS TABLE 
 
AS
BEGIN
        DECLARE @TABLE_NAME varchar(50)
        set @TABLE_NAME = (select Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID)
        
        DECLARE @SQL VARCHAR(200)
 
        SET @SQL = 'select * from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
 
		EXEC sp_executesql @SQL
 
END
GO

Select allOpen in new window

Syntactical Mistake, Check here for more info:

http://msdn.microsoft.com/en-us/library/ms186755(SQL.90).aspx

CREATE FUNCTION TBN
(       
        @RequestTypeID int,
        @RequestSerialNo int
)
RETURNS TABLE 
AS
RETURN(
        DECLARE @TABLE_NAME varchar(50);
        DECLARE @SQL VARCHAR(200);
        set @TABLE_NAME = (select Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID);
 
        SET @SQL = 'select * from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
 
        EXEC sp_executesql @SQL;
);
GO

Select allOpen in new window

rrjegan17
Your code gives me the following error:
Msg 156, Level 15, State 1, Procedure TBN, Line 9
Incorrect syntax near the keyword 'DECLARE'.
Msg 170, Level 15, State 1, Procedure TBN, Line 16
Line 16: Incorrect syntax near ')'.
Also, I went to the link you provided, and I saw that this is the format I need (Check code body below)
I don't have (RETURNS @return_variable TABLE < table_type_definition >) in the code I originally posted neither in the code you provided as instructed in the template below

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

Select allOpen in new window

observe
"RETURNS @return_variable TABLE < table_type_definition >"in above template. I have already told you that you will need tabledefination, don't you have fixed table structure to return? dynamictable name will work but you have to have table defination if you wantsto return table.

Select allOpen in new window

RiteshShah,
    You are correct. Function wont be able to return the result executed by sp_executesql. We have to insert the records into the temp table and then select the output from that table.

Try this one out:

CREATE FUNCTION TBN
(       
        @RequestTypeID int,
        @RequestSerialNo int
)
RETURNS TABLE 
AS
RETURN(
        DECLARE @TABLE_NAME varchar(50),
                @SQL NVARCHAR(200);
        set @TABLE_NAME = (select Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID);
--        create table #temp (all your columns) 
        SET @SQL = 'insert into #temp select * from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
 
        EXEC sp_executesql @SQL;
        select * from #temp;
);
GO

Select allOpen in new window

rrjegan17, Yes, needs to have table definition but I would like to go with predefined table definition before AS rather than TEMP table, just want to stick with syntax, no major reason.

RiteshShah
Ok, I just made another modification to the code according to what you instrusted, and the function has been successfully created (check code below)
Now, when I try to call the function, I apply this command:
select Request_Serial_No from TBN(2,20)
When I do so, it gives me the following error:
Msg 245, Level 16, State 1, Procedure TBN, Line 18
Syntax error converting the varchar value 'select Request_Serial_No from Request2_Log where Request_Serial_No=' to a column of data type int.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
 
 
CREATE FUNCTION TBN
(
        @RequestTypeID int,
        @RequestSerialNo int
)
RETURNS 
@Table_Variable_Name TABLE 
(
	Request_Serial_No int
)
AS
BEGIN
        DECLARE @TABLE_NAME varchar(50);
        DECLARE @SQL VARCHAR(200);
		set @TABLE_NAME = (select Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID);
        SET @SQL = 'select Request_Serial_No from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
		EXEC sp_executesql @SQL;
	RETURN
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Select allOpen in new window

use:

convert(varchar,@RequestSerialNo)

you are passing integer value in @RequestSerialNo and use it with "+" operator with varchar so compiler gets confused so while giving it there, convert it to varchar and you will be ok.

RiteshShah
I have changed:
SET @SQL = 'select Request_Serial_No from ' +  @TABLE_NAME + ' where Request_Serial_No=' + @RequestSerialNo;
to:

SET @SQL = 'select Request_Serial_No from ' + @TABLE_NAME + ' where Request_Serial_No=' + convert(varchar,@RequestSerialNo);
 
Then I got the following error:

Msg 557, Level 16, State 2, Procedure TBN, Line 19
Only functions and extended stored procedures can be executed from within a function.

Sorry, I don't have SSMs access right now so can't check sysntax but can you tell me whether you got above error at runtime or at compile time? moreover, you can declare @RequestSerialNo as varchar in your definition, that will also solve your problem

I just changed:

CREATE FUNCTION TBN
(
@RequestTypeID int,
@RequestSerialNo int
)
 
to:

CREATE FUNCTION TBN
(
@RequestTypeID int,
@RequestSerialNo varchar(10)
)
 
and I ran this command:
select Request_Serial_No from TBN(2,'20')
I still got the same error message in my last post
Just not to go any further in any possible wrong direction, my DB is MS SQL 2005

I'm sorry, but no matter how hard you try you won't get dynamic SQL to work in a function in SQL 2005 - and I don't see it working in SQL 2008 either.

You'll get it to compile, but it won't run the dynamic SQL.

I suggest you consider the alternatives.

If you will always be returning the same table structure, the if condition method I posted earlier could work.

Among the possible solutions, you could use a view so that your query would look like

select ColumnList
from ViewName
where Request_Serial_No = @Request_Serial_No

You could also use a stored procedure.

But you can't use dynamic SQL in a function.

Here's the sample of the function solution, including some sample tables:

create table ThisTable (Request_Serial_No int not null, T1_Stuff nvarchar(50))
create table ThatTable (Request_Serial_No int not null, T2_Stuff nvarchar(50))

insert ThisTable (Request_Serial_No, T1_Stuff) values (5, 'This g')
insert ThisTable (Request_Serial_No, T1_Stuff) values (10, 'This a')
insert ThisTable (Request_Serial_No, T1_Stuff) values (20, 'This b')

insert ThatTable (Request_Serial_No, T2_Stuff) values (10, 'That a')
insert ThatTable (Request_Serial_No, T2_Stuff) values (20, 'That b')
insert ThatTable (Request_Serial_No, T2_Stuff) values (30, 'That c')
insert ThatTable (Request_Serial_No, T2_Stuff) values (40, 'That d')

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBN]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TBN]
GO

CREATE FUNCTION dbo.TBN
(
    @RequestTypeID int,
    @RequestSerialNo nvarchar(10)
)
RETURNS
@Table_Variable_Name TABLE
(
      Request_Serial_No int
)
AS
BEGIN
      DECLARE @TABLE_NAME varchar(50), @SQL VARCHAR(200);
      select @TABLE_NAME = Request_Table_Name from dbo.Request_Type where Request_Type_ID=@RequestTypeID;

if @TABLE_NAME = 'ThisTable'
begin
insert @Table_Variable_Name
select Request_Serial_No from ThisTable where Request_Serial_No = @RequestSerialNo
end

if @TABLE_NAME = 'ThatTable'
begin
insert @Table_Variable_Name
select Request_Serial_No from ThatTable where Request_Serial_No = @RequestSerialNo
end

RETURN
END
GO