SQL dynamic query: check whether table column has any data

Question

I do have dynamically generated tables list, user can modify those tables ex: add columns and remove columns, add data etc..

I need to modify this stored procedure, when user needs to drop particular column from specific table, there I need to first check whether column has data, if no data user can alter table. To achieve this condition I wrote the code but it doesn't work:

DROP TABLE IF EXISTS #TABLEDATACOUNT

CREATE TABLE #TABLEDATACOUNT(C BIGINT)

DECLARE @dataCountQuery NVARCHAR(MAX)='SELECT COUNT(*) AS C FROM ['+@DynamicTable+']'

INSERT INTO #TABLEDATACOUNT 
    EXEC sys.sp_executesql @dataCountQuery

IF EXISTS (SELECT * FROM #TABLEDATACOUNT WHERE C>0)
BEGIN
    INSERT INTO @RESULT (ID, [MESSAGE]) 
    VALUES (1, 'Data exists!')

    -- INSERTING NEW COLUMNS
    IF(LEN(@fields) = 0)
        GOTO EXITSP

    DECLARE @index1 BIGINT = 0

    WHILE(@index1 < (SELECT COUNT(*) FROM OPENJSON(@fields)))
    BEGIN
        DECLARE @fieldJson1 NVARCHAR(MAX)=(SELECT [VALUE] FROM OPENJSON(@fields) WHERE [KEY]=@index1)
        DECLARE @fieldName1 NVARCHAR(MAX)=(SELECT [VALUE] FROM OPENJSON(@fieldJson1) WHERE [KEY]='name')
        DECLARE @fieldType1 NVARCHAR(MAX)=(SELECT [VALUE] FROM OPENJSON(@fieldJson1) WHERE [KEY]='type')

        IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=+'['+@DynamicTable+'] AND COLUMN_NAME='+@fieldName1)=0)
            BEGIN
                DECLARE @insertSql NVARCHAR(MAX)=('ALTER TABLE ['+@DynamicTable+'] ADD ['+@fieldName1+'] '+@fieldType1)
                EXEC sys.sp_executesql @insertSql
                INSERT INTO @RESULT (ID,[MESSAGE]) VALUES (3,'Column inserted')
            END
            
            IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=+'['+@DynamicTable+'] AND COLUMN_NAME='+@fieldName1)=1)
            BEGIN
            -- Here I need Your support
            -- If Block CHECK table field has no data then user can drop the column
            IF((N'SELECT MAX(['+@fieldName1+']) FROM  ['+@dynamicTableName+']')= 'NULL')
            DECLARE @dropfieldSql NVARCHAR(MAX)=('ALTER TABLE ['+@DynamicTable+'] DROP COLUMN ['+@fieldName1+'])
            EXEC sys.sp_executesql @dropfieldSql
            --INSERT INTO @RESULT (ID,[MESSAGE]) VALUES (3,'Column inserted')
            END
            SET @index1=@index1+1
        END
    END
ELSE
    -- This I managed

in the above code

Here I need your support

-- If Block CHECK table field has no data then user can drop the column
   IF((N'SELECT MAX(['+@fieldName1+']) FROM  ['+@dynamicTableName+']')= 'NULL')

Jonathan Willcock · Accepted Answer · 2021-06-21 05:25:04Z

3

You are not executing the test. You need something like:

declare @rowcheck int = 0;
declare @rowchecksql nvarchar(1000) = N'SELECT @rows=1 WHERE EXISTS (SELECT ['+@fieldName1+'] FROM ['+@dynamicTableName+'] WHERE ['+@fieldName1+'] IS NOT NULL);';
exec sp_executesql @rowchecksql, N'@rows INT OUTPUT', @rows=@rowcheck OUTPUT;

Then you can continue with:

IF @rowcheck = 0
    BEGIN
        ...;
    END

Note that we have to create an OUTPUT parameter to the dynamic sql and then assign this parameter to a variable that we have already declared. Then when we execute the dynamic sql, the variable is assigned the value, and we can then examine it. Note also that COUNT is better here than MAX.

EDIT: Thanks to @Charlieface for pointing out that EXISTS is better still (as the table search will stop as soon as a single record is found meeting the condition).

edited Jun 21, 2021 at 5:25

answered Jun 20, 2021 at 7:27

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL dynamic query: check whether table column has any data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related