user defined function causing error

Question

Post reply

user defined function causing error

triloba

Mr or Mrs. 500

Points: 582
More actions
March 28, 2016 at 2:32 pm

#314417

pseudo code:
create function udf1 (
@one int,
@two int)
as
--- test this udf: select * from udf1(123, 456)
Returns @temptbl TABLE (...)
insert into @temptbl SELECT ...
insert into @temptbl SELECT ...
update @temptbl
Return @temptbl
When I run select * from udf(123, 456)
The error is:
Msg 245, Level 16, State 1, Line 32
Conversion failed when converting the varchar value '10-45' to data type int.
When I comment out the 'create function ...' and make other minor changes, etc, I can run all the code and it returns the temp table rows successfully.
The '10-45' from the error message is selected from a varchar column and is being inserted into a varchar column in @temptbl. so it's not that simple.
Are there settings that stay with the function code (from creation) that could be different when I run the individual commands?
How do I see which settings are different? or besides comparisons between character & int values, what should I look for?
Thanks in advance,
Tom

Viewing 7 posts - 1 through 6 (of 6 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Lets see real code please, not pseudo code.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

triloba Mr or Mrs. 500 Points: 582 More actions · Answer 2

I'm sorry I wasn't clear.

I'm not asking anyone to look through the code. I'm asking what causes it to run differently when run as a function verses when run as a batch. The same code fails when we select from table-valued function, but runs successfully as a batch.

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No idea, because the error thrown doesn't match the code.

Could be missing begin/end, could be some of those 'other minor changes' you made. Without seeing the code that throws the error, we're guessing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

dba-wannabe (3/29/2016)
I'm sorry I wasn't clear.
I'm not asking anyone to look through the code. I'm asking what causes it to run differently when run as a function verses when run as a batch. The same code fails when we select from table-valued function, but runs successfully as a batch.
Thanks,

It's using a different execution plan and different way to access the data which makes it encounter the invalid row. Why do you have the value '10-45' in a supposed integer column? If it's valid, why are you comparing it against an integer instead of comparing it against a string?

Are you aware of the performance problems of multi-statement table-valued functions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

triloba Mr or Mrs. 500 Points: 582 More actions · Answer 5

'10-45' is not stored in a supposedly integer column. I'm not the expert, but I don't think I can store '10-45' in an integer column. It's selected from a varchar column and stored in a varchar col in the temp table. In the maze of udf's and views, we don't see anywhere that it's being compared to an int or converted to Int.

Thanks for the performance tip, but performance isn't the/an issue. The code has been around for years and will just have to do for now.

Please don't waste your time explaining 'different way to access the data '. That's the concept I was looking for, but now that we have a work-around, I have to move on.

thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

dba-wannabe (3/29/2016)In the maze of udf's and views

That is one of the scariest things I have read this week. Do you have nested views? Do you have scalar functions? Do you have lots more of these multi-statement table valued functions? All of these things are a recipe for really unbelievable bad performance. And creating a maze of stuff like this is a sign that there are some more potential design problems lurking just under the surface.

_______________________________________________________________

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/