I would like to create a function that......

• guarddata

  SSCertifiable

  Points: 6583

  More actions

  September 2, 2003 at 5:21 pm

  #472280

  Sorry Lenard. Perhaps if we understood more of the background we could help find another solution. Sounds like you are pretty adept. Good Luck!

  Guarddata-

• lenardd

  Default port

  Points: 1435

  More actions

  September 2, 2003 at 11:51 pm

  #472293

  GuardData,

  I ended up using a cursor approach. What I do, is read in my list an item at a time, "normalize" it (the part that I was hoping the function would do), and stick it into a temp table. When that process ends, I simply return the temp table to the calling program.

  Worked out quite nice, I might add. Performance is not a issue because I only need to process about 20 items. Took me 2 days to work out this problem out but, hey, I learned how to do cursors and OUT parameters with SP_EXECUTESQL! 🙂

  Thanks for your time.

  --Lenard

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  September 3, 2003 at 12:48 am

  #472294

  Hi Lenard,

  quote:

  ---

  I ended up using a cursor approach. What I do, is read in my list an item at a time, "normalize" it (the part that I was hoping the function would do), and stick it into a temp table. When that process ends, I simply return the temp table to the calling program.

  Worked out quite nice, I might add. Performance is not a issue because I only need to process about 20 items. Took me 2 days to work out this problem out but, hey, I learned how to do cursors and OUT parameters with SP_EXECUTESQL! 🙂

  ---

  actually this is what I like most on this forum.

  I'm learning everyday something new! Never dived that deep inside sp_executesql , but that's another story.

  For almost every problem there is a solution and together we'll manage to get there.

  If possible, Lenard, could your post your solution so I (we) can keep for future reference ?

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• lenardd

  Default port

  Points: 1435

  More actions

  September 3, 2003 at 3:34 am

  #472329

  Sure Frank....I'd be glad to. This approach is a little specific to my needs so you will have to read between the "lines". First, some setup info to help you understand it:

  
  
  @ACID is the user's id. 
  
  @Mask will be something like:   Email (Home): {0}  I have 20 of these that I read in....like for Mobile, Fax, etc.
  
  @SqlText will be something like:   SELECT @val = Email FROM Contacts WHERE ACID = @key     Same here.....one for every mask.
  
  
  
  
  
  CREATE    PROCEDURE dbo.spTeam_GetContactPreferences
  
  
  
  @ACID int
  
  
  
  AS
  
  SET NOCOUNT ON
  
  
  
  DECLARE @val nvarchar(100)
  
  DECLARE @returnTable table ( ID int, Name varchar(100 ) )
  
  DECLARE @id int, @mask varchar(100), @sql nvarchar(100)
  
  
  
  DECLARE sourceTable CURSOR FAST_FORWARD
  
  FOR SELECT ID, Mask, SQLText FROM ContactPreferences
  
  
  
  OPEN sourceTable
  
  
  
  WHILE @@FETCH_STATUS = 0 BEGIN
  
  FETCH NEXT FROM sourceTable INTO @id, @mask, @sql
  
  EXEC dbo.sp_executesql @sql, N'@key int, @val nvarchar(100) OUTPUT', @key = @ACID, @val = @val OUTPUT
  
  
  
    IF( LEN(@val) > 0 )
  
  INSERT @returnTable ( ID, Name )  VALUES ( @id, REPLACE( @mask, '{0}', @val ) )
  
  
  
  SET @val = ''
  
  
  
  END
  
  
  
  CLOSE sourceTable
  
  DEALLOCATE sourceTable
  
  
  
  SELECT * FROM @returnTable
  
  
  
  GO
  
  
  
   

  If you have any questions, let me know.

  --Lenard