Solved

store proceudre or funcion table type:

Posted on 2011-03-01
10
335 Views
Last Modified: 2012-05-11
i have this procedure
   CREATE PROC Production.LongLeadProducts
AS
  SELECT      Name, ProductNumber
  FROM      Production.Product
  WHERE      DaysToManufacture >= 1
GO

I was reading.
recommend that converts sp to  the function of such a table, I wonder why?
0
Comment
Question by:enrique_aeo
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 35010418
Where did you read this?

Stored procedure and function have their own scope and usage. Such a conversion is never done by SQL Server.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 225 total points
ID: 35010461
Honestly, I likely wouldn't use a procedure or a function for such a simple select. If anything, MAYBE a view.  You have no input criteria so I see no reason for it to be a procedure/function.
0
 

Author Comment

by:enrique_aeo
ID: 35010569
I ATTACHED THE FILE
rewritingSP.jpg
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35010591
You are reading someone's opinion.
0
 
LVL 8

Assisted Solution

by:Som Tripathi
Som Tripathi earned 113 total points
ID: 35010687
Enrique,

I think you have asked this question earlier two times -

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26547587.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26689460.html

What interesting research you are doing, please let us know.. :)
0
 

Author Comment

by:enrique_aeo
ID: 35011198
so is a friend, now I want to know why a store procedure to select should be a function of the type table
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35011470

It should not be either. You are simply displaying data, there are no parameters, inserts or edits and your where clause is constant
Just use a view as suggested earlier
0
 

Author Comment

by:enrique_aeo
ID: 35020361
actually it is a sp with parameters, so I can not use views
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 112 total points
ID: 35020879

it would have been better if you provided a real representation of the SP

If you want to join the results to a different table then you can convert it to a table valued function
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 225 total points
ID: 35022084
You can still have "parameters" on views.  You just add a where clause to the select.  I'm not saying it's always that simple, but sometimes it is.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
Sql Query Datatype 2 23
Anyway to make these 2 SQL statements into one? 13 38
sql 2008 how to table join 2 15
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question