Using a UDF to provide a value for an insert statement

Question

Post reply

Using a UDF to provide a value for an insert statement

MothInTheMachine

Hall of Fame

Points: 3251
More actions
June 24, 2009 at 1:05 pm

#75488

Hi,
I have a stored procedure that is bringing code over form a legacy system. That system had a column called 'type' defined as a varchar and contained data like 'Computer' or 'Class'.
I have written a function, fnType, that takes in that varchar value and returns an integer corresponding to the type on our current system. The value in I am passing to the function is from a cursor, if that matters, it too is defined as a varchar.
I'd like to use the udf returned value in my insert statement like:
Insert into newtable (trainingType, otherCols) values (fnType(@varcharType), OtherValues)
or
Insert into newtable (trainingType, otherCols ) values (select fnType(@varcharType), OtherValues)
Could someone help me with the proper syntax to use in this case? I've not been able to find a solution via google or by searching this form/site.
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

functions almost always need to be preceded with the owner/schema

so either of these should work:

Insert into newtable (trainingType, otherCols)

values (dbo.fnType(@varcharType), OtherValues)

or

Insert into newtable (trainingType, otherCols)

select dbo.fnType(@varcharType), OtherValues

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 2

It's that easy? Wow...I actually guessed right...lol..that doesn't happen very often 🙂

Thanks!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

WayneS SSC Guru Points: 95460 More actions · Answer 3

Whilst I won't pretend that I know your system better than you, might I suggest you change the way that you are doing this?

If you put your varchar values into a table, with your corresponding integer value, then you could easily run a joined insert statement.

Something like this:

declare @Tmp table (

[Type] varchar(10),

TypeNbr int)

insert into @Tmp

select 'Computer', 1 UNION ALL

select 'Class', 2

insert into newtable (trainingType, otherCols)

select @Tmp.typeNbr, otherCols

from @Tmp

where [Type] = @varcharType

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 4

Actually this is a script we're using to load data from a legacy system into our current system and don't need to store the old varchars. Once we do the import the script and those damned varchars will be gone forever...I hope.:hehe:

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;