How to get a value from func/sp to a case statement using dynamic SQL?

Question

hoping someone might be able to help me with a bit of an issue. Essentially i'm trying to get a rough size of all of the fields in my database as i'd like to do some math on it to guesstimate what the size will be with a compression technique applied to it.

I can do this for most fields by looking at the datatype and using the number of rows to get the number of bytes it's taking up. However on something like a varchar(max) field this is not as easy and so i decided to approach this by getting the average length within the column and multiplying by number of rows. But i've hit a snag which i'll describe below.

I have the following stored proc (i tried a function too but you can't call dynamic SQL from a function).

CREATE PROCEDURE dbo.getFieldSize(@column varchar(255), @table varchar(255), @ret decimal(15,7) OUTPUT) AS BEGIN DECLARE @lengthSQL varchar(50) /SET @lengthSQL = 'SELECT @ret = AVG(DATALENGTH(' + @column + ')) FROM [' + @table +']'/ SET @lengthSQL = 'SELECT @ret = AVG(DATALENGTH(' + @column + ')) FROM ' + @table exec sp_executesql @lengthSQL RETURN @ret END GO

And then i call it using...

SELECT b.TABLE_SCHEMA as 'Schema', CASE WHEN DATA_TYPE IN ('nvarchar') AND CHARACTER_MAXIMUM_LENGTH <> -1 AND c.distinctItems <> 0 AND c.totalCount <> 0 THEN exec('select max(len(' + b.TABLE_CATALOG + ' + '.' + ' + b.COLUMN_NAME + '))') FROM .... The above is basically just checking to make sure it is a varchar(max) field and contains some values within the column. I then try and execute the SP and pass the column name and table name for which i need the avg length but i get the following error.

Msg 156, Level 15, State 1, Line 57 Incorrect syntax near the keyword 'exec'.

I learned you cannot call a dynamic SQL from a function and you cannot call a SP from a CASE statement. So at this point it seems like it's a catch 22 and i cannot do what i need using SQL. Can anyone think of any workarounds or i'm I out of luck on this?

Answer 1

Actually, you can do Dynamic SQL in a scalar UDF, it just needs to be a SQLCLR UDF ;-). But this is fairly simple to do using the in-process / internal connection (i.e. SqlConnection("Context Connection = true;");). Meaning, the assembly can be set to SAFE.

Also, object / column / index names are all NVARCHAR. And objects (if not also the others) are declared as sysname which is an alias for NVARCHAR(128). Just FYI.

So, something like the following (which I have tested and it does work):

[Microsoft.SqlServer.Server.SqlFunction(Name = "GetAvgBytes",
    IsDeterministic = false, IsPrecise = true, DataAccess = DataAccessKind.Read)]
public static SqlInt32 GetAvgBytes([SqlFacet(MaxSize = 128)] SqlString TableName,
    [SqlFacet(MaxSize = 128)] SqlString ColumnName)
{
    int _AvgBytes = -1;
    SqlConnection _Connection = new SqlConnection("Context Connection = true;");
    SqlCommand _Command = _Connection.CreateCommand();
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SELECT @AvgBytes = AVG(DATALENGTH(" + ColumnName.Value
                        + ")) FROM " + TableName.Value + " WITH (NOLOCK);";

    SqlParameter _Param = new SqlParameter("@AvgBytes", DbType.Int32);
    _Param.Direction = ParameterDirection.Output;
    _Command.Parameters.Add(_Param);

    try
    {
        _Connection.Open();

        _Command.ExecuteNonQuery();

        _AvgBytes = (int)_Param.Value;
    }
    finally
    {
        _Connection.Close();
    }

    return _AvgBytes;
}