Posted on Leave a comment

Invalid Column Error When One Stored Proc Calls Another

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.

Posted on Leave a comment

Vertical Text

filter: flipv fliph;
-o-transform: rotate(270deg);

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

Posted on Leave a comment


DECLARE @obj TABLE ( id int identity, objname sysname, objtype sysname )
DECLARE @i int
, @sql nvarchar(max)

INSERT INTO @obj ( objname, objtype )
, (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 )

SELECT @i = max(id) FROM @obj

WHILE @i > 0
SELECT @sql = 'DROP ' + A.objtype + ' ' + A.objname
FROM @obj A
WHERE = @i
PRINT @sql
SET @i = @i - 1

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.