execution of function with tvp - read only

Question

Post reply

execution of function with tvp - read only

gxdatareal

Ten Centuries

Points: 1022
More actions
February 29, 2016 at 1:00 am

#324558

..

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

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

gxdatareal Ten Centuries Points: 1022 More actions · Answer 1

gxdatareal

Ten Centuries

Points: 1022

February 29, 2016 at 1:20 am

#1861732

)

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

This is how you normally do it:

DECLARE @params AS @myTVP

INSERT INTO @params VALUES (

0, --SomeCode,

0, --SomeWeight

)

EXEC dbo.DIS @params

-- Gianluca Sartori

gxdatareal Ten Centuries Points: 1022 More actions · Answer 3

THANKS

Is it possible in order to run the function to use

SELECT * FROM dbo.DIS ('12', '33')

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

spaghettidba

SSC Guru

Points: 105732

February 29, 2016 at 5:15 am

#1861757

Nope

-- Gianluca Sartori

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

The reason you can't pass individual parameter values is that the function is accepting a table, not individual values.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

This is a multi-statement TVP. Those should be avoided at all costs. DEVASTATINGLY BAD stuff happens! Also, this one is completely ridiculous. Simply calculate the sum yourself on your on object in your own code.

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

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

I agree 100% with Kevin that this function is ridiculous. As he said this a horrible way to build a table valued function from a performance standpoint. After your insert into the return you declare a new variable and then set it to the sum of weight. What is the point of that?

Do you realize that when you pass in a table valued parameter it behaves exactly like a table. You have a number of unnecessary steps in here. I don't get the point of the cte at all. It is nothing but a select *. And you really don't even need to bother with the local table variable anyway. All you are doing there is creating another copy of the inbound data. If you want greatly streamline the function you posted you could shorten it to this.

CREATE FUNCTION dbo.DIS

(

@myTVP Code_List READONLY

)

RETURNS TABLE AS RETURN

select sum(weight) as dis_code

from @myTVP

_______________________________________________________________

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/