Month: January 2012

I’ve run into a problem where one stored procedure calls another and I get an “Invalid Column” error. The child stored proc works fine when run separately.

Some similar errors could easily be cleaned up by using sp_recompile or sp_refreshsqlmodule.

But, turns out there is a little bug in the column name validation when there are temp tables in a child stored proc that have the same name as a temp table in the parent proc.

Solution: rename the temp table in one proc or another. Or, could use variable tables or some other structure in one or the other.

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

http://msdn.microsoft.com/en-us/library/ms131048.aspx

     .verticaltext

     {

        writing-mode:tb-rl;

        filter: flipv fliph;

        -webkit-transform:rotate(270deg);

        -moz-transform:rotate(270deg);

        -o-transform: rotate(270deg);

        white-space:nowrap;

     }
     .heading

     {

        background: #000000;

        color: #FFFFFF;

        font-weight: bold;

        height: 90px;

     }
















  
Column-A
  
Column-B
  
Column-C


  
Row 1 Col A
  
Row 1 Col B
  
Row 1 Col C


  
Row 2 Col A
  
Row 2 Col B
  
Row 2 Col C

DECLARE @obj TABLE ( id int identity, objname sysname, objtype sysname )

DECLARE @i int

      , @sql nvarchar(max)

INSERT INTO @obj ( objname, objtype )

          SELECT A.name

               , (case when A.type = 'V' then 'VIEW'

                       when A.type in ('FN','IF','TF') then 'FUNCTION'

                       when A.type = 'P' then 'PROCEDURE'

                       else '' end )

            FROM sysobjects A (NOLOCK)

           WHERE A.type IN ( 'V', 'FN', 'IF', 'TF', 'P' )

        ORDER BY (case A.type when 'V' then 3

                              when 'P' then 1

                              else 2 end )

               , A.name DESC
SELECT @i = max(id) FROM @obj
WHILE @i > 0

 BEGIN

   SELECT @sql = 'DROP ' + A.objtype + ' ' + A.objname

     FROM @obj A

    WHERE A.id = @i

   PRINT @sql

   SET @i = @i - 1

 END

This is one example where using a table variable and while loop on a relatively small set of data is preferred, simply because the "sysobjects" table will be mutating through the process of the loop. Could still get away with it in a cursor if locks and hints are properly applied, but I still prefer to avoid it when possible.

sqlwithmanoj demonstrated cases where using optimization hints such as FAST_FORWARD, FORWARD_ONLY, READ_ONLY can result in cursors that are more performant than while loops on table variables.

http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/