sqlforge

Friday, January 27, 2012

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.

Friday, January 13, 2012

Enable CLR

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

Thursday, January 12, 2012

Vertical Text



<HTML>
<HEAD>
  <STYLE>
     .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;
     }
  </STYLE>
</HEAD>


<BODY>

<TABLE BORDER="1"><TR class="heading">
  <TH class="verticaltext">Column-A</TH>
  <TH class="verticaltext">Column-B</TH>
  <TH class="verticaltext">Column-C</TH>
</TR>
<TR>
  <TD>Row 1 Col A</TD>
  <TD>Row 1 Col B</TD>
  <TD>Row 1 Col C</TD>
</TR>
<TR>
  <TD>Row 2 Col A</TD>
  <TD>Row 2 Col B</TD>
  <TD>Row 2 Col C</TD>
</TR>
</TABLE>
</BODY>

</HTML>

Wednesday, January 4, 2012

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.

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/

Monday, December 5, 2011

Update Statistics

DECLARE @tabs TABLE (  id int identity(1,1) not null
                     , tabname sysname not null );

DECLARE @i int
      , @tabname sysname;

   INSERT INTO @tabs ( tabname )
        SELECT name
          FROM sysobjects
         WHERE type = 'U';

SELECT @i = max(id) FROM @tabs;

WHILE @i > 0
 BEGIN
   SELECT @tabname = tabname FROM @tabs WHERE id = @i
   PRINT 'UPDATE STATISTICS ' + @tabname + ' WITH ALL'
   SET @i = @i - 1
 END;

GO

ISNULL, COALESCE

1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

2. The NULLability of result expression can be different for ISNULL and COALESCE. ISNULL return value is considered NOT NULLable if the return value is a non-nullable one (in the case when the argument that is returns is based on a non-null column or constant). Whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

ISNULL(NULL, NULL) -- is int

COALESCE(NULL, NULL) -- Will throw an error

COALESCE(CAST(NULL as int), NULL) -- it valid and returns int

4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. You have to have nested ISNULL to get the same effect as COALESCE.

5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL built-in function.

6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and some queries with COALESCE be sub-optimal.