Capabilities

sp_CascadingDataViewer can
  • Return the number of rows that are dependant on a given row (or set of rows) as defined by a filter condition (i.e. Show me all dependants WHERE colA = <some-value>)
  • Display all of the dependant data as well as returning the total number of dependant rows. (simply set @pShowData=1)
  • Allow the user to specify multiple columns in the filter column (i.e. Show me all dependants WHERE colA = <some-value> AND colB = <another-value>)
  • handle self-referencing tables
  • handle a row having more than one reason to be a dependant row (read more)
  • display the dynamic SQL that is being generated under the covers (simply set @pPrintSQL=1)
  • Display the contents of temporary tables & table variables that get created during execution. This is mainly for the benefit of us developer(s) but if you want to see what sp_CascadingDataViewer is doing under the covers then this may prove useful. (simply set @pDebug=1)

Limitations

sp_CascadingDataViewer can/will not
  • (currently) handle specification of columns in the @pFilterCols parameter that are not of type int, tinyint, smallint, bigint, varchar, nvarchar, char or nchar. Any dependant tables that have columns of any other types are fine - they will not be affected.
  • work on any version of SQL Server prior to SQL Server 2008
  • cope with any values in @pFilterValues that contain commas. This is due to the ability to select multiple columns in @pFilterCols and hence multiple values in @pFilterValues- the values in @pFilterValues are comma-delimited and hence any commas that exist within the values themselves will result in an error: There must be the same count of values in @pFilterCols as there are columns in @pFilterValues. Note that sp_CascadingDataViewerTableWrapper handles this by not calling sp_CascadingDataViewer on any such values and instead returning NULLs in its resultset.

Behaviour to be aware of

sp_CascadingDataViewer
  • The final number of dependant rows includes any rows in the specified table that match the filter criteria specified by @pFilterCols & @pFilterValues. In other words, sp_CascadingDataViewer will never return 0 unless the filter criteria specifies some non-existent rows.
  • constructs tables in the database that you specify then removes them at the end of processing Please be aware of this one sp_CascadingDataViewer will not attempt to create any tables if it does not have permission to and in this case it will fail gracefully, however just be aware that ordinarily it will be creating some tables.
  • I stated in Capabilities that sp_CascadingDataViewer handles the situation where a row in a dependant table has more than one reason to be a dependant row. There is a caveat to this, if the dependant table does not have a PRIMARY KEY or UNIQUE KEY defined then sp_CascadingDataViewer will not be able to determine if a row is already a dependant row or not. This makes sense if you think about it but nonetheless, be aware.
  • *sp_CascadingDataViewer does not pass a return value, Any information (including the number of dependant rows) is passed out either by OUTPUT prameters or resultset.

Input Validation

sp_CascadingDataViewer carries out the following checks on the input values prior to execution:
  • Check input values are non-zero length
  • Check specified database exists
  • Check the execution security context has the ability to create tables
  • Check specified table exists
  • Check number of specified columns equals number of specified values
  • Check specified columns exist in the specified table
  • Check datatype(s) of column(s) are of type int, tinyint, smallint, bigint, varchar, nvarchar, char or nchar
  • Check the same column has not been specified twice

This page is under construction

Last edited Aug 10, 2010 at 10:15 PM by jamiet, version 31

Comments

No comments yet.