SQL Server : table-valued function does not work with sub query

Question

I have a SQL Server table-valued function which is created by me I have pasted the create script below

CREATE FUNCTION [dbo].[getTableFromString]
(       
    @String AS nvarchar(max)

)
RETURNS @ReturnTable TABLE ( StringValues nvarchar(10)  )   
AS begin 

    if (SELECT CHARINDEX(',', @String)) = 0
        begin
                insert into @ReturnTable (StringValues) values (subString(@String,1,len(@String)));
        end 
    else
        begin 
            while (SELECT CHARINDEX(',', @String)) > 0
            begin 
                insert into @ReturnTable (StringValues) values (subString(@String,1,CHARINDEX(',', @String)-1));
                set @String  = subString(@String,CHARINDEX(',', @String)+1,len(@String));

                if (SELECT CHARINDEX(',', @String)) = 0
                begin
                        insert into @ReturnTable (StringValues) values (subString(@String,1,len(@String)));
                end         
            end 
        end

    return ;
end

and I am using this function like below

Select sum(NetSales)
from vwxsalesall
where Company = 'rs'
and storecode = (select cPrimaryStockRoomCode from CompanyMaster.CompanyProfileDetail where cCompanyNo = 'rs' and cSecondaryStockRoomCode = 'R01B')
and trandate >= '2012-01-01'
and trandate <= '2012-01-31'
and ( 
        brand in (  
                    select StringValues from  dbo.getTableFromString(
                                                                    select vIncludedBrandCodes
                                                                    from StockRoomTargetData.MonthlyTarget 
                                                                    where cCompanyNo = 'rs' 
                                                                    and cSecondaryStockRoomCode = 'R01B'
                                                                    and nYear = 2012 
                                                                    and nMonth = 8
                                                                    )  
                 ) 
    )

Unfortunately I am getting this error

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

please help me

You need to put `select vIncludedBrandCodes ...` subquery into parenthesis to indicate to Sql Server that this is in fact a subquery. — Nikola Markovinović, Aug 08 '12 at 12:06

score 0 · Accepted Answer · answered Aug 08 '12 at 13:12

0

You need to add another set of parenthesis around subquery, for instance

select StringValues from  dbo.getTableFromString(( { your-subquery }))

                                                 ^                  ^
                                                 (parenthesis added)

First set of parenthessis syntactically belong to TVF invocation, and second denote a subquery.

And now your query:

Select sum(NetSales)
from vwxsalesall
where Company = 'rs'
and storecode = (select cPrimaryStockRoomCode from CompanyMaster.CompanyProfileDetail where cCompanyNo = 'rs' and cSecondaryStockRoomCode = 'R01B')
and trandate >= '2012-01-01'
and trandate <= '2012-01-31'
and ( 
        brand in (  
                    select StringValues from  dbo.getTableFromString((
                                                                    select vIncludedBrandCodes
                                                                    from StockRoomTargetData.MonthlyTarget 
                                                                    where cCompanyNo = 'rs' 
                                                                    and cSecondaryStockRoomCode = 'R01B'
                                                                    and nYear = 2012 
                                                                    and nMonth = 8
                                                                    )) 
                 ) 
    )

answered Aug 08 '12 at 13:12

Nikola Markovinović

18,963
5
46
51

Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '('. Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ')'. – Prabhakantha Aug 09 '12 at 05:50
@Prabhakantha It works for me, [see it yourself](http://sqlfiddle.com/#!3/e1dc5/1). Which version of Sql Server are you using? – Nikola Markovinović Aug 09 '12 at 07:27
@Prabhakantha Could you please post your current query if it is still not working? – Nikola Markovinović Aug 09 '12 at 07:32
i am using sql 2005 with compatibility level 80 and i cannot change the compatibility level in my database as some of the reports goes to not working state :'( – Prabhakantha Aug 09 '12 at 07:41
@Nicola I Just Pasted your query since you are using the same table names it should work for me :) – Prabhakantha Aug 09 '12 at 07:42
@Prabhakantha I cannot find inability to use subquery as a parameter in Sql Server 2000 mentioned anywhere. Could you try with simple example? `select StringValues from dbo.getTableFromString((select 'xyz'))`? If it is not working I'm afraid you will have to cross join subquery to main query and use `vIncludedBrandCodes` from there. – Nikola Markovinović Aug 09 '12 at 07:52
@Prabhakantha Please check [the answer to this question](http://stackoverflow.com/questions/5103624/incorrect-syntax-using-a-table-valued-function-in-select-clause-of-t-sql-query). It seems that my proposed workaround will not work at all. – Nikola Markovinović Aug 09 '12 at 07:54
@Nicola you are correct select StringValues from dbo.getTableFromString((select 'xyz')) does not work's as well :) ... seems i have to find a work around for this :) by the way thanks for the help :) now i know its some thing to do with compatibility level :) – Prabhakantha Aug 09 '12 at 08:38
@Prabhakantha I'm sorry I could not help you more. – Nikola Markovinović Aug 09 '12 at 08:54

SQL Server : table-valued function does not work with sub query

1 Answers1