Results 1 to 3 of 3

Thread: Help please

  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Unanswered: Help please

    I have the following code and this brings back any column which contains non null values.

    Code:
    declare @col varchar(255), @cmd varchar(max)--, @Test varchar(max)
    
    DECLARE getinfo cursor for
    SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
    WHERE t.Name = 'ErrorTable'
    
    OPEN getinfo
    
    FETCH NEXT FROM getinfo into @col
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	--set @Test = @col
        SELECT @cmd = 'IF NOT EXISTS (SELECT * FROM ErrorTable WHERE Qname = ''Canx Orders with Missing Reason or Date'' 
    	AND [' + @col + '] IS NULL and Fixed = 0 and datepart(dd,RunDate)= datepart(dd,getdate()) 
        and datepart(mm,RunDate)= datepart(mm,getdate()) and datepart(yy,RunDate)= datepart(yy,getdate())) 
    
    	BEGIN print ''' + @col + ''' end'
    
        EXEC(@cmd)
    
        FETCH NEXT FROM getinfo into @col
    END
    
    CLOSE getinfo
    DEALLOCATE getinfo
    The result is the following

    Qname
    CHANGEBY
    CHANGEDATE
    RunDate
    USERCODE
    USERTYPE
    CUSTOMSTRING1
    Fixed

    I then need to get the results into the below code as column names instead of using the * as my table contains 80 column names

    Code:
    SELECT *
    FROM ErrorTable 
    WHERE Fixed = 0 and datepart(dd,RunDate)= datepart(dd,getdate()) 
    and datepart(mm,RunDate)= datepart(mm,getdate()) and datepart(yy,RunDate)= datepart(yy,getdate()) 
    AND Qname = 'Canx Orders with Missing Reason or Date'
    Please could someone help me with this.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well it's not just null...is it

    Code:
    IF NOT EXISTS (SELECT * 
    				 FROM ErrorTable 
    				WHERE Qname = 'Canx Orders with Missing Reason or Date'
    				  AND [' + @col + '] IS NULL 
    				  AND Fixed = 0 
    				  AND datepart(dd,RunDate)= datepart(dd,getdate()) 
    				  AND datepart(mm,RunDate)= datepart(mm,getdate()) 
    				  AND datepart(yy,RunDate)= datepart(yy,getdate())) 
    
    	BEGIN print ''' + @col + ''' end
    And how is this suppose to work anyway?

    Why don't you just look to see if they have nulls first?

    What are the other columns for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    in any case

    Code:
    DECLARE @cmd varchar(max)
    
    SET @cmd = ''
    
    SELECT @cmd = @cmd +  
    		'SELECT DISTINCT ' + '''' + COLUMN_NAME + '''' + ' AS COL_NAME ' 
    	  + 'FROM ' + TABLE_NAME + ' WHERE ' + COLUMN_NAME + ' IS NULL UNION ALL '
      FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_NAME = 'myTable99'
    ORDER BY TABLE_NAME, ORDINAL_POSITION
    
    SELECT @cmd = SUBSTRING(@cmd,1,LEN(@cmd)-10)
    
    SELECT @cmd
    
    EXEC(@cmd)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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