Return field names and if there is a value in the field 1

[Kflasph]

I don't know if this is even possible to do but here it goes. I have to write a program (via VFP 6) that shows if information in the table has been filled by the user. Using a treeview, the user wants to see if the field has information - show the field name in green, if not show the field name in red.
Any ideas?

Thanks in advance!
KFlasph

 

[SQLBill]

What is VFP? Visual Fox Pro?

I suggest posting in an appropriate forum. This is for Microsoft SQL Server which uses Transact-SQL.

-SQLBill

Posting advice: FAQ481-4875

 

[Kflasph]

Maybe I should have explained better - I want to build a stored procedure to return the field name and the values and then use the information returned to VFP. I need to if there is a way to do this within SQL stored procedure since it is an SQL table that contains my information.

Kflasph

 

[SQLBill]

Can you give us an example of your data and what you want returned?

I think a CASE statement that checks for NULLs might be what you want. SQL Server won't return colors, but it could return other values that your front-end could convert to what you want to see.

-SQLBill

Posting advice: FAQ481-4875

 

[Kflasph]

This is what I want to return from the stored procedure:

I have a sql table with 40 fields plus.
I want to return only the name of each field and if that field has a value in it based on the parameter I pass to query the table.
Such as:
Select field_name,field_value from sql_table where field_value=parameter_passed.


Once I get that data, I can parse it within my calling program

Kflasph

 

[dbomrrsm]

hopefully this will be a start:

--To search all columns of all tables in Pubs database for the keyword "Computer" 
--EXEC SearchAllTables 'Computer'
--GO 

--Here is the complete stored procedure code: 


CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

[blue]DBomrrsm[/blue]