Bapineedu Bollempalli

 Profile Answers by Bapineedu Bollempalli 

• Sep 21st, 2011

 

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 won’t 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.