Posted on

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

Vertical Text

.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

Posted on

DROP all VIEWS, FUNCTIONS, PROCEDURES

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.

Posted on

Using Cursors Optimally

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/