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ć
– Nikola Markovinović, Commented Aug 8, 2012 at 12:06

Nikola Markovinović · Accepted Answer · 2012-08-08 13:12:09Z

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 8, 2012 at 13:12

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Prabhakantha Over a year ago

Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '('. Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ')'.

Nikola Markovinović Over a year ago

@Prabhakantha It works for me, see it yourself. Which version of Sql Server are you using?

Nikola Markovinović Over a year ago

@Prabhakantha Could you please post your current query if it is still not working?

Prabhakantha Over a year ago

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 Over a year ago

@Nicola I Just Pasted your query since you are using the same table names it should work for me :)

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related