Stored procedures are not parameterized views

re: Stored procedures are not parameterized views

Jamie Thomson — Fri, 25 May 2007 13:35:37 GMT

Adam,

As I have mentioned to you before, I love this blog post. Whenever I am arguing with someone about the use of sprocs I just point them staight at this and pretty soon they are building UDFs.

Just wanna say thanks (again) for writing this.

-Jamie

re: Stored procedures are not parameterized views

Gideon Kahl — Tue, 02 Dec 2008 16:05:37 GMT

Thanks a lot. I've been trying to solve a SSIS problem the whole day, and this has solved it. I've changed my stored proc to a function and now things are working.

(I am just confused why in other SSIS packages the stored procs worked...)

re: Stored procedures are not parameterized views

Max — Wed, 14 Jan 2009 20:08:52 GMT

Hi Adam,

I have to agree with you that UDFs are a better solution for the OUTPUT contract. But, I'd like to mention that this kind of discussion always opens that can of worms: should you or should you not use SPs? That's where the discussion goes retarded(no, not parochial, retarded). Working with big databases will always force you to use SPs, but once you agreed that SPs are wrong...

Max

re: Stored procedures are not parameterized views

An Phu — Mon, 06 Jul 2009 18:54:53 GMT

TVF should always be used if you are simply reading from tables (and not doing inserts, deletes, updates).

There are other advantages with TVF: Avoiding code duplication and minimizing network traffic.

Take for example, you have two apps, app1 and app2. Both apps queries against the same table but app1 only needs columns 1-5; app2 needs columns 2, 3 and 6-10.

With SPs, you either have to either write two SP that does essentially the same thing but select different columns or modify the SP signature and add conditional logic to format the output.

For each app, you can simply select the columns you need from the TVF. The TVF will return only the columns you want. No code duplication, no extra columns/row returned.

re: Stored procedures are not parameterized views

Stephen Hand — Mon, 01 Mar 2010 11:51:55 GMT

Thanks, I was thinking this was the case but your post summed it all up nice and clearly.

Just to throw this in, as of SQL Server 2005 I think INFORMATION_SCHEMA.ROUTINE_COLUMNS contains info about all the columns returned in TVFs.

re: Stored procedures are not parameterized views

Jason Yousef — Wed, 28 Sep 2011 13:14:06 GMT

Thanks for writing this, helped me one day and today I referred someone to it to fix his problem.