Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Views/Stored-Procedure/User-Defined function
- Thread starter RicoCali
- Start date
NoCoolHandle
Programmer
A view is a predefined select statement that works just like a table... Only it can be a many table join.. It supports Where clauses, selecting specific columns, column manipulation and ordering as well as aggregation..
It even supports the indexing of an aggregation.
A Stored Proceedure
if a predifined sql statement that can also store "Logic"..
It can return rows as well as columns. Inside its "logic boundary" you can create temporary tables, varaiables, update tables, delete from tables, insert into tables...
It can take and return parameters, this makes it very usefull for often run querys or big insert statements.
It doesn't let you add a where clause, cant be used as the source of a subquery and can't be joined.
A Function
is very much like a combination of the best features of Views and StoredPRocs..
They can contain multiple tables joined together to return a resultset (like a multitable join or view) they accept and return parameters (as resultsets)
They can be joined with other tables, views or functions inside an adhoc query.
You can specify what columns you wnat returned from them
They can be used like a paramtitzed view in Oracle..
HTH
Rob
PS ask more questions if any of this doesn't make sence
It even supports the indexing of an aggregation.
A Stored Proceedure
if a predifined sql statement that can also store "Logic"..
It can return rows as well as columns. Inside its "logic boundary" you can create temporary tables, varaiables, update tables, delete from tables, insert into tables...
It can take and return parameters, this makes it very usefull for often run querys or big insert statements.
It doesn't let you add a where clause, cant be used as the source of a subquery and can't be joined.
A Function
is very much like a combination of the best features of Views and StoredPRocs..
They can contain multiple tables joined together to return a resultset (like a multitable join or view) they accept and return parameters (as resultsets)
They can be joined with other tables, views or functions inside an adhoc query.
You can specify what columns you wnat returned from them
They can be used like a paramtitzed view in Oracle..
HTH
Rob
PS ask more questions if any of this doesn't make sence
All of these are ways to query or manipulate information from tables or perform calculations. Views are the simplest and the least useful. They in general do not improve performance except when you are using indexed views. They do not allow you to dynamically change the parameters of the where clause at run-time. What they do is allow you to write complex joins or calculations once and reuse in other ways or to limit the users to a particular set of data without giving them rights to the whole table. While some views are updateable, updating them can be extremely tricky and it is best to avoid where possible.
Stored procedures are where the meat of your queries and processes belong. They are often the most efficient way SQL Server has of manipulating data. Anything done in a view can also be done in a stored procedure except the use of an indexed view. (an indexed view can be refernced ina a stored procedure though.) In addition, stored procedures allow you to set parameters at run time and even to create SQL statments dynamically at run-time. (This should be avoided where possible as it is not efficient or easy to effectively test.) You can also use procedural code if needed for complex processing. You can do loops and cursors (again avoid if at all possible as these are very inefficient). You can use transaction processing when performing muliple tranasctions in one stored procedure.
The purpose of a user defined (or system) function is to write the code for repetitive calculations or processing once and use it multiple times. A function can return a single value or a table. Functions can be run from within select statments to perform the function on every record in the select. Table values functions can be inserted into temp tables within a stored procedure. An example of a function I use frequently is one that calculates the latitudes and longitudes for a square around a particular point so that we can find other items of interest near that point. Since this is a very complex caclulation involving all sorts of trig fuctions and since we use it in multiple places in our application, this was a clear cut case of the need for a function. We did it once, we thoroughly tested it and we can use it in new places with confidence that it works properly. Further, with functions, if you need to change them later as the business rules change, you only need to change them in one place and all the stored procedures calling the function will reflect the change.
Questions about posting. See faq183-874
Stored procedures are where the meat of your queries and processes belong. They are often the most efficient way SQL Server has of manipulating data. Anything done in a view can also be done in a stored procedure except the use of an indexed view. (an indexed view can be refernced ina a stored procedure though.) In addition, stored procedures allow you to set parameters at run time and even to create SQL statments dynamically at run-time. (This should be avoided where possible as it is not efficient or easy to effectively test.) You can also use procedural code if needed for complex processing. You can do loops and cursors (again avoid if at all possible as these are very inefficient). You can use transaction processing when performing muliple tranasctions in one stored procedure.
The purpose of a user defined (or system) function is to write the code for repetitive calculations or processing once and use it multiple times. A function can return a single value or a table. Functions can be run from within select statments to perform the function on every record in the select. Table values functions can be inserted into temp tables within a stored procedure. An example of a function I use frequently is one that calculates the latitudes and longitudes for a square around a particular point so that we can find other items of interest near that point. Since this is a very complex caclulation involving all sorts of trig fuctions and since we use it in multiple places in our application, this was a clear cut case of the need for a function. We did it once, we thoroughly tested it and we can use it in new places with confidence that it works properly. Further, with functions, if you need to change them later as the business rules change, you only need to change them in one place and all the stored procedures calling the function will reflect the change.
Questions about posting. See faq183-874
- Thread starter
- #4
- Thread starter
- #5
NoCoolHandle
Programmer
<SNIP>RicoCali (Programmer) Aug 10, 2004
> It doesn't let you add a where clause, cant
> be used as the source of a subquery and
> can't be joined
For stored procedures? I think you can
</SNIP>
Only inside the proc..
you cant execute like procGetNames where lastname = 'smith'
but you can procGetNames @lastname = 'smith' if it has a parameter called @lastname and then that parameter will get passed to the select inside the proc...
HTH
Rob
> It doesn't let you add a where clause, cant
> be used as the source of a subquery and
> can't be joined
For stored procedures? I think you can
</SNIP>
Only inside the proc..
you cant execute like procGetNames where lastname = 'smith'
but you can procGetNames @lastname = 'smith' if it has a parameter called @lastname and then that parameter will get passed to the select inside the proc...
HTH
Rob
- Status
Similar threads