stored procedures and functions

Functions

can be used with Select statement

Not returning output parameter but returns Table variables

You can join UDF

Can not be used to change server configuration

Can not be used with XML FOR clause

Can not have transaction within function



Stored Procedure

have to use EXEC or EXECUTE

return output parameter

can create table but wont return Table Variables

you can not join SP

can be used to change server configuration

can be used with XML FOR Clause

can have transaction within SP

SSIS Designer is a graphical tool for creating packages that includes separate tabbed design surfaces for building the control flow, data flow, and event handlers in packages.

Transact-SQL (T-SQL) stored procedures should be treated just like reusable application code. You should follow these suggested guidelines to ensure that your stored procedures are solid and robust: Check all parameters for valid values and return an error message if a problem exists.


Be sure that the parameter data types match the column data types they are compared against to avoid data type mismatches and poor query optimization.


Check the @@error system function after each SQL statement, especially insert, update, and delete, to verify that the statements executed successfully. Return a status code other than 0 if a failure occurs.


Be sure to comment your code so that when you or others have to maintain it, the code is self-documenting.


Consider using a source code management system, such as Microsoft Visual Studio SourceSafe, CVS, or Subversion, to maintain versions of your stored procedure source code.