?
Solved

SQL Function Creation Error

Posted on 2009-04-27
18
Medium Priority
?
355 Views
Last Modified: 2012-08-13
Hello

I'm new to writing functions in SQL

What's wrong with this code (check code section below), why does it give me the following error message:

Msg 137, Level 15, State 2, Procedure TBN, Line 15
Must declare the variable '@TABLE_NAME'.
Msg 170, Level 15, State 31, Procedure TBN, Line 17
Line 17: Incorrect syntax near 'BEGIN'.
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)
	RETURN 
	(
		select * from @TABLE_NAME where Request_Serial_No=@RequestSerialNo
	)
END
GO

Open in new window

0
Comment
Question by:alfardan
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 12

Expert Comment

by:expertsoul
ID: 24247552
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

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24247570
One slight modification to expertsoul's comment.

Instead of EXECUTE (@SQL)
Use

EXEC sp_executesql @SQL

Because EXECUTE clause will be removed in future versions of SQL Server 2005.
MSDN for your reference.
http://msdn.microsoft.com/en-us/library/ms188332(SQL.90).aspx
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24247606
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:GSQL
ID: 24247701
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
0
 

Author Comment

by:alfardan
ID: 24247868
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

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24247894
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

Open in new window

0
 

Author Comment

by:alfardan
ID: 24248036
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
[ ; ]

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24248093
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.

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24248269
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

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24248325
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.
0
 

Author Comment

by:alfardan
ID: 24248349
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

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24248361
use:

convert(varchar,@RequestSerialNo)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24248368
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.
0
 

Author Comment

by:alfardan
ID: 24248427
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.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24248453
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
0
 

Author Comment

by:alfardan
ID: 24248500
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
0
 
LVL 3

Expert Comment

by:GSQL
ID: 24249645
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.
0
 
LVL 3

Expert Comment

by:GSQL
ID: 24249791
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
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question