the difference between Stored procedures temporary tables

[Sally123]

hi

I am new in SQL Scritpting and my question might sound stupid, but please help me out to understand.

Can one explain the difference between the two and similarity?

The need and and use of the two?

As for Temporary table does one create them or it comes with SQL.

Regards
Sally

[dickersonka]

a stored procedure is a sql statement that is stored in the database for all queries / tables to be able to have access to (with permissions)

they can return results, update, or whatever you normally do through a query

a temp table is temporary holding place for values, where you can actually select from that table, then it is deleted without affecting your regular database structure

they are normally used in more complicated queries, while stored procedures are used simple such as inserts along with complicated queries as well

[sierrainfo]

Stored Procedure : Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to aplication developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Views: Database views allow you to create "virtual tables" that are generated on the fly when they are accessed. A view is stored on the database server as an SQL statement that pulls data from one or more tables and (optionally) performs transformations on that data. Users may then query the view just as they would any real database table. Views are often used to alleviate security concerns by providing users with access to a certain view of a database table without providing access to the underlying table itself.

Views

1. Does not accepts parameters
2. Can be used as a building block in large query.
3. Can contain only one single Select query.
4. Can not perform modification to any table.
5. Can be used (sometimes) as the target for Insert, update, delete queries

Stored Procedure

1. Accept parameters
2. Can not be used as a building block in large query.
3. Can contain several statement like if, else, loop etc.
4. Can perform modification to one or several tables.
5. Can not be used as the target for Insert, update, delete queries.

[Sally123]

thank for your respond

Is the use of Cursor related to temporary table. what is the difference between the two?

Regards
Sally

[Sally123]

Stored Procedure : Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to aplication developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Views: Database views allow you to create "virtual tables" that are generated on the fly when they are accessed. A view is stored on the database server as an SQL statement that pulls data from one or more tables and (optionally) performs transformations on that data. Users may then query the view just as they would any real database table. Views are often used to alleviate security concerns by providing users with access to a certain view of a database table without providing access to the underlying table itself.

Views

1. Does not accepts parameters
2. Can be used as a building block in large query.
3. Can contain only one single Select query.
4. Can not perform modification to any table.
5. Can be used (sometimes) as the target for Insert, update, delete queries

Stored Procedure

1. Accept parameters
2. Can not be used as a building block in large query.
3. Can contain several statement like if, else, loop etc.
4. Can perform modification to one or several tables.
5. Can not be used as the target for Insert, update, delete queries.

Thank for your respond

Is the use of Cursor related to temporary table. what is the difference between the two?

Regards
Sally

[dickersonka]

cursor is related to looping through a result set

a cursor is best thought of as a foreach loop for sql, you can loop through either a temp table or an actualy table