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

Question

Post reply

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

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

guarddata SSCertifiable Points: 6583 More actions · Answer 1

guarddata

SSCertifiable

Points: 6583

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 · Answer 2

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 · Answer 3

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 · Answer 4

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