Results 1 to 15 of 15
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Where Clause with VariableName

    How can I achieve the following in SQL:

    SELECT
    @CURRCOLUMN,
    COUNT(*)
    FROM
    CUSTOMERS
    WHERE
    @CURRCOLUMN = ""

    It gives me an error. I basically need to loop through an entire tables variables to find out occurances of the column being either "" or null and return me a count of the number of occurances. This is just a small part of that task.

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Red face This is not how to write SQL

    How can I achieve the following in SQL:

    SELECT @local_variable_of_unknown_type, COUNT(*)
    FROM Customers
    WHERE @local_variable_of_unknown_type= "";

    It gives me an error.
    The @ tells us this is a local variable or parameter; either way it resolves to a scalar value. The double quotes enclose the name of a data element, but since you cannot have an empty for a data element name, this is garbage.

    I basically need to loop {sic} through an entire tables variables to find out occurrences of the column being either "" {impossible column name} or NULL and return me a count of the number of occurrences. This is just a small part of that task.
    Loop??! SQL is a declarative, so we do not use loops.
    Tables have rows; rows have columns; they do not have variables.
    Nothing you posted makes sense.
    If you want to count the number of NULLs in a column, use this skeleton:

    SELECT real_column_name, COUNT(*) - COUNT(real_column_name) AS null_cnt
    FROM Table_Name
    GROUP BY real_column_name;

    The cardinality includes NULLs; the expression count drops them.

  3. #3
    Join Date
    May 2008
    Posts
    97
    its "" or NULL.

    Can I get a count on both using your method?

    results should be like
    ========================================
    COLUMN_NAME|NULL_COUNT|EMPTY_STRING_COUNT|

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only way that I know to get counts for every column, with counts for NULL, and counts for a specific value (in this case an empty string) is to generate dynamic SQL.

    I understand that this is part of a larger process. Can you describe how that larger process will use the information that you're trying to get? I expect that there is a better way, but I don't know enough about your problem to help you find that better way yet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2008
    Posts
    97
    the data is for me to be run as a script.

    I have about 30+ tables and some of which have 45 columns. I need a high level overview of this information without manually testing every single column

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    "" is not '' but your can use
    SELECT SUM(CASE WHEN string_col = '' THEN 1 ELSE 0 END) AS empty_cnt
    FROM Table_Name

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Pat Phelan View Post
    The only way that I know to get counts for every column, with counts for NULL, and counts for a specific value (in this case an empty string) is to generate dynamic SQL.

    -PatP
    Feel free to modify as needed.

    Code:
    /*
    	PURPOSE:  This procedure (or code fragment) can be used to count the data in each
    		  user defined table by [server].[database].[owner].[table] and put the results into
    		  a crosstab table.
    
    	Created by: 	Jon L. Campbell
    	Create date:	04/11/2004
    
    	Notes:	  Change (add or delete) servers in domain as required.  
    
    */
       Set quoted_identifier off
       Set nocount on
    
    -- declare local variables
       DECLARE @db varchar(255)
       DECLARE @serverSQL varchar(255)
       DECLARE @serverVSQL varchar(255)
       DECLARE @serverDSQL varchar(255)
       DECLARE @tablename varchar(255)
       DECLARE @sql nvarchar(525)
    
    -- create a temporary table to hold results of table count
       CREATE TABLE #DATACOUNT(dbname varchar(255),tablename varchar(255),tablecount int)
    
    -- declare a cursor
    DECLARE tablenames_cursor CURSOR FOR SELECT Name FROM sysobjects where xtype = 'U' order by Name
    OPEN tablenames_cursor
      FETCH NEXT FROM tablenames_cursor INTO @tablename
      WHILE (@@fetch_status <> -1)
        BEGIN
            SET @serverSQL = '[SERVER]'  <-- ENTER SERVER NAME
            SET @serverSQL = @serverSQL + '.TABLENAME.DBO.' + @tablename  <-- ENTER TABLE NAME
            SET @SQL = "INSERT INTO #DATACOUNT "
            SET @SQL = @SQL +  "SELECT 'DB' = 'RecordingMgmt'" + ",'Table'  = '" + RTRIM(UPPER(@tablename)) + "','Total' = count(*) from " + RTRIM(UPPER(@serverSQL))
            EXEC sp_executesql @SQL
    
       		PRINT @SQL
    
            FETCH NEXT FROM tablenames_cursor INTO @tablename
          END
    DEALLOCATE tablenames_cursor
    
    SELECT DISTINCT TableName,tablecount as 'Count'
    FROM #DATACOUNT
    
    DROP TABLE #DATACOUNT

  8. #8
    Join Date
    May 2008
    Posts
    97
    Very cool! Thanks

  9. #9
    Join Date
    Feb 2004
    Posts
    193
    What do you mean by saying SQL is "declarative?"

    thanks

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't think of any declarative way to do what he wants to do, other than generating a single massive dynamic SQL statement.
    Looping through the tables with individual dynamic sql, as corncrowe illustrated, is the most practical solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I can't think of any declarative way to do what he wants to do, other than generating a single massive dynamic SQL statement. Looping through the tables with individual dynamic SQL , as corncrowe illustrated, is the most practical solution.
    Agreed; this is a meta data problem, not SQL at all.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Lightweights!
    Code:
    SELECT
       CASE
          WHEN 1 != a.ORDINAL_POSITION
          THEN ''
          ELSE 'SELECT ' + QuoteName(a.TABLE_CATALOG, '''') + ' AS "TABLE_CATALOG", ' 
    +        QuoteName(a.TABLE_SCHEMA, '''') + ' AS "TABLE_SCHEMA", ' 
    +        QuoteName(a.TABLE_NAME, '''') + ' AS "TABLE_NAME", '
    +        'Count(*) AS "AllRows"'
       END
    +  CASE WHEN 'NO' = IS_NULLABLE THEN '' ELSE ', Count(CASE WHEN ' 
    +     QUOTENAME(a.COLUMN_NAME) + ' IS NULL THEN 1 END) AS ' 
    +     QuoteName('NULL' + a.COLUMN_NAME, '"') END
    +  CASE
          WHEN a.DATA_TYPE NOT IN ('varchar', 'nvarchar') THEN ''
          ELSE ', Count(CASE WHEN '''' = ' 
    +        QUOTENAME(a.COLUMN_NAME) + ' THEN 1 END) AS ' 
    +        QuoteName('Empty' + a.COLUMN_NAME, '"') END
    +  CASE
          WHEN a.ORDINAL_POSITION = (SELECT MAX(z1.ORDINAL_POSITION)
             FROM INFORMATION_SCHEMA.COLUMNS AS z1
             WHERE  z1.TABLE_CATALOG = a.TABLE_CATALOG
                AND z1.TABLE_SCHEMA  = a.TABLE_SCHEMA
                AND z1.TABLE_NAME    = a.TABLE_NAME)
          THEN '   FROM ' 
    +           QUOTENAME(a.TABLE_CATALOG) + '.' 
    +           QUOTENAME(a.TABLE_SCHEMA) + '.'
    +           QUOTENAME(a.TABLE_NAME)
          ELSE ''
       END
       FROM INFORMATION_SCHEMA.COLUMNS AS a
       ORDER BY a.TABLE_CATALOG, a.TABLE_SCHEMA, a.TABLE_NAME, a.ORDINAL_POSITION
    Note that I never said it was a good idea, pretty, or much of anything else... I just said "Lightweights"!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Pat, ran your code snippet and it produces sql which has to be ran manually and produces single select rows. Too much workie...

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Lightweights!
    I believe I already covered that option:
    Quote Originally Posted by blindman View Post
    ...other than generating a single massive dynamic SQL statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •