June 20, 2011 at 12:54 pm
create Function fn_test(@TestCol int)
returns table
as
If (@TestCol =1)
select 'Abc' as Col1
else
select 'xyz' as Col1
-----Gets the following error
Msg 156, Level 15, State 1, Procedure fn_test, Line 6
Incorrect syntax near the keyword 'If'.
What is wrong in the above syntax.
June 20, 2011 at 1:49 pm
Well you are attempting to create a multi statement table function but your syntax is that of Inline table valued function. You will have to define the layout of the table inside your return declaration.
I found a decent article that explains it here
To make your code functional I tweaked it a little like so:
create Function fn_test
(
@TestCol int
) returns @myTable
table
(
Col1 varchar(20)
)
as begin
--I make it a habit to alway have functions and sproc contents wrapped with a begin/end to avoid
--accidental batch overruns by forgetting a go in deployment scripts
If (@TestCol =1)
insert @myTable
select 'Abc' as Col1
else
insert @myTable
select 'xyz' as Col1
return
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2011 at 1:54 pm
satishchandra (6/20/2011)
create Function fn_test(@TestCol int)returns table
as
If (@TestCol =1)
select 'Abc' as Col1
else
select 'xyz' as Col1
-----Gets the following error
Msg 156, Level 15, State 1, Procedure fn_test, Line 6
Incorrect syntax near the keyword 'If'.
What is wrong in the above syntax.
If you declare the return type as TABLE with no TABLE definition then you are telling SQL Server you want an inline table-valued function (iTVF). This means that you cannot have any conditional statements and the function must immediately return a table. Like this:
USE AdventureWorks
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fn_test')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.fn_test ;
GO
CREATE FUNCTION dbo.fn_test ()
RETURNS TABLE
AS
RETURN
(
SELECT name
FROM sys.tables
) ;
GO
SELECT *
FROM dbo.fn_test() ;
If you need an IF condition then you'll be forced to switch to a multi-statement table-value function (mTVF) and provide a table definition. Like this:
USE AdventureWorks
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fn_test')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.fn_test ;
GO
CREATE FUNCTION dbo.fn_test
(
@table_name SYSNAME = NULL
)
RETURNS @my_table TABLE (table_name SYSNAME)
AS
BEGIN
IF @table_name IS NOT NULL
BEGIN
INSERT INTO @my_table
(
table_name
)
SELECT name
FROM sys.tables
WHERE name = @table_name ;
END
ELSE
BEGIN
INSERT INTO @my_table
(table_name)
SELECT name
FROM sys.tables ;
END
RETURN ;
END
GO
SELECT *
FROM dbo.fn_test(N'Store') ;
SELECT *
FROM dbo.fn_test(NULL) ;
It is worth noting that iTVFs are the preferred TVF in terms of performance. In your scenario it would be ideal if you created two different iTVFs, one each for each purpose, instead of one mTVF that accepts a parameter.
EDIT: Sean and I were typing at the same time. The link he included has a good overview.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 10:01 pm
Okay, I think I can write the original code as an iTVF:
create function dbo.fn_test(@TestCol int)
returns table
as
return(select case when @TestCol = 1 then 'Abc'
else 'xyz'
end as Col1);
go
select * from dbo.fn_test(1);
select * from dbo.fn_test(2);
go
drop function dbo.fn_test;
go
Now, if you would like to tell us what it is you are really attempting to do, we would be more than happy to help you.
June 24, 2011 at 10:13 am
All the suggested solutions worked. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply