Questions about User-Defined Function which return Table-Value

• thinknight

  SSCrazy

  Points: 2068

  More actions

  May 12, 2011 at 12:45 pm

  #238033

  I have a table Customer like:

  id Name Surname

  --------------------------------------

  1 Name1 Surname1

  2 Name2 Surname2

  3 Name3 Surname3

  And I have a function like:

  alter function TestFunc(@xxId nvarchar(1024),

  @testStr varchar(512)

  )

  RETURNS @Results TABLE

  (

  id int,

  name varchar(512)

  )

  as

  BEGIN

  INSERT INTO @Results

  select 1 as A, Surname from Customer where name in (@testStr)

  RETURN

  END

  go

  But while executing this query:

  select * from TestFunc('XXX','(''Name1'',''Name2'')')

  There is no result

  What should be Select statement:

  select 1 as A, Surname from Customer where name in (@testStr)

  ???

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 12, 2011 at 1:31 pm

  #1323940

  This is because there is no record where name = '(''Name1'',''Name2'')'

  You can't use a single variable like that. You will have to split this variable first. You should check out this super cool function from Jeff Moden.

  And here is thread discussing your exact issue and a resolution for it.

  _______________________________________________________________

  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/