SQL Function Prob

• tadhg88

  Ten Centuries

  Points: 1091

  More actions

  August 29, 2007 at 7:22 am

  #95390

  Hi i am trying to create a function that will return a string containing information about matches this is what i have created so far but the result always seems to be null im just wondering if anyone has any ideas why this would be happening

  Thanks in advance

  Tim

  CREATE FUNCTION [dbo].[pr_bll_GetNextSixGames]

   (@PlayerID int) returns nvarchar(100)

  --WITH ENCRYPTION

  BEGIN

  DECLARE @result nvarchar(100)

  DECLARE @Match nvarchar(100)

  DECLARE rs CURSOR FOR

  -- get list of match names

  SELECT homeTeam.Name + ' v ' + awayTeam.Name as 'Match'

  FROM Match m

  Join ScoutAssignment sa on sa.MatchID = m.ID

  Join Squad homeSquad on homeSquad.ID = m.SquadA

  Join Squad awaySquad on awaySquad.ID = m.SquadB

  Join Club homeTeam on homeTeam.ID = homeSquad.ClubID

  Join Club awayTeam on awayTeam.ID = awaySquad.ClubID

  Order By m.Date

  OPEN rs

  FETCH NEXT FROM rs INTO @Match

  SELECT @result = @result + @Match

  WHILE @@Fetch_Status = 0

  BEGIN

   SELECT @result = @result + @Match

   FETCH NEXT FROM rs INTO @Match

  END

  CLOSE rs

  DEALLOCATE rs

  return @result

  END

• bledu

  SSChampion

  Points: 12335

  More actions

  August 29, 2007 at 7:28 am

  #730086

  if you have a null value in homeTeam.Name or awayTeam.Name the soln will be null try using

  isnull(homeTeam.Name,'') + ' v ' + isnull(awayTeam.Name ,'')

  ---

  Everything you can imagine is real.
  

• bledu

  SSChampion

  Points: 12335

  More actions

  August 29, 2007 at 7:29 am

  #730090

  or for SELECT @result = @result + @Match

  try

  SELECT @result = isnull(@result,'') + isnull(@Match,'')

  ---

  Everything you can imagine is real.
  

• tadhg88

  Ten Centuries

  Points: 1091

  More actions

  August 29, 2007 at 7:33 am

  #730095

  that second one solved the problem perfectly

  thanks alot its very much appreciated

  Tim