Passing a subquery as a parameter to a user defined function

I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>