How to correct the query??

Question

How to correct the query??

shaikhansar786

SSC Enthusiast

Points: 134
More actions
March 13, 2017 at 1:41 am

#401774

Hello Experts,
i want to write a query such that the only columns needs to be selected from the table columns belongs to the below datatypes.
('nvarchar','varchar','char')
can any one help me in resolving this.
although i have tried with below query but the end result is not as expected.
my end result bringing integer as well.
set @ColumnDataType='varchar';
IF @ColumnDataType in('nvarchar','varchar','char')
Begin
Set @QueryDataType = 'INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT {BulkLoadId}
,''Invalid data type for column {ColumnName}: ''+ cast({ColumnName} as varchar)+ ''; Expected Data Type:{ColumnDataType}''
,''1''
FROM {TableName} t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id(''{TableName}'')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.{ColumnName}, ''BaseType'') not in (''varchar'',''nvarchar'',''char'') then 1 else 0 end)
AND t.{ColumnName} IS NOT NULL AND t.BulkLoadId={BulkLoadId}';
SET @QueryDataType =REPLACE(@QueryDataType , '{BulkLoadId}', @BulkLoadId);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnName}', @ColumnName);
SET @QueryDataType =REPLACE(@QueryDataType , '{TableName}', @TableName);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnDataType}', @ColumnDataType);
PRINT @QueryDataType
below is the generated statement .
INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT 445
,'Invalid data type for column IDCOL: '+ cast(IDCOL as varchar)+ '; Expected Data Type:varchar'
,'1'
FROM TblBulkload_PermanentDeInstallTemplate t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id('TblBulkload_PermanentDeInstallTemplate')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.IDCOL, 'BaseType') not in ('varchar','nvarchar','char') then 1 else 0 end)
AND t.IDCOL IS NOT NULL AND t.BulkLoadId=445
i want the t.IDCOL should not come into picture.
also there more than lakhs of records are getting generated from the above query.
i have 25000 rows in the above table.
and each row consists of 12 columns , so the total count becomes 300000.
but all are not having integer datatype.
please anyone help me in resolving this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

shaikhansar786 - Monday, March 13, 2017 1:41 AM
Hello Experts,
i want to write a query such that the only columns needs to be selected from the table columns belongs to the below datatypes.
('nvarchar','varchar','char')
can any one help me in resolving this.
although i have tried with below query but the end result is not as expected.
my end result bringing integer as well.
set @ColumnDataType='varchar';
IF @ColumnDataType in('nvarchar','varchar','char')
Begin
Set @QueryDataType = 'INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT {BulkLoadId}
,''Invalid data type for column {ColumnName}: ''+ cast({ColumnName} as varchar)+ ''; Expected Data Type:{ColumnDataType}''
,''1''
FROM {TableName} t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id(''{TableName}'')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.{ColumnName}, ''BaseType'') not in (''varchar'',''nvarchar'',''char'') then 1 else 0 end)
AND t.{ColumnName} IS NOT NULL AND t.BulkLoadId={BulkLoadId}';
SET @QueryDataType =REPLACE(@QueryDataType , '{BulkLoadId}', @BulkLoadId);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnName}', @ColumnName);
SET @QueryDataType =REPLACE(@QueryDataType , '{TableName}', @TableName);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnDataType}', @ColumnDataType);
PRINT @QueryDataType
below is the generated statement .
INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT 445
,'Invalid data type for column IDCOL: '+ cast(IDCOL as varchar)+ '; Expected Data Type:varchar'
,'1'
FROM TblBulkload_PermanentDeInstallTemplate t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id('TblBulkload_PermanentDeInstallTemplate')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.IDCOL, 'BaseType') not in ('varchar','nvarchar','char') then 1 else 0 end)
AND t.IDCOL IS NOT NULL AND t.BulkLoadId=445
i want the t.IDCOL should not come into picture.
also there more than lakhs of records are getting generated from the above query.
i have 25000 rows in the above table.
and each row consists of 12 columns , so the total count becomes 300000.
but all are not having integer datatype.
please anyone help me in resolving this.

What exactly is your generated statement supposed to do? Can you post up some sample data, also the results you expect to see from the sample data.
The list - ss.system_type_id in (35,99,167,175,239) - omits NVARCHAR.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shaikhansar786 SSC Enthusiast Points: 134 More actions · Answer 2

ChrisM@Work - Monday, March 13, 2017 4:52 AM
shaikhansar786 - Monday, March 13, 2017 1:41 AM
Hello Experts,
i want to write a query such that the only columns needs to be selected from the table columns belongs to the below datatypes.
('nvarchar','varchar','char')
can any one help me in resolving this.
although i have tried with below query but the end result is not as expected.
my end result bringing integer as well.
set @ColumnDataType='varchar';
IF @ColumnDataType in('nvarchar','varchar','char')
Begin
Set @QueryDataType = 'INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT {BulkLoadId}
,''Invalid data type for column {ColumnName}: ''+ cast({ColumnName} as varchar)+ ''; Expected Data Type:{ColumnDataType}''
,''1''
FROM {TableName} t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id(''{TableName}'')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.{ColumnName}, ''BaseType'') not in (''varchar'',''nvarchar'',''char'') then 1 else 0 end)
AND t.{ColumnName} IS NOT NULL AND t.BulkLoadId={BulkLoadId}';
SET @QueryDataType =REPLACE(@QueryDataType , '{BulkLoadId}', @BulkLoadId);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnName}', @ColumnName);
SET @QueryDataType =REPLACE(@QueryDataType , '{TableName}', @TableName);
SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnDataType}', @ColumnDataType);
PRINT @QueryDataType
below is the generated statement .
INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
SELECT 445
,'Invalid data type for column IDCOL: '+ cast(IDCOL as varchar)+ '; Expected Data Type:varchar'
,'1'
FROM TblBulkload_PermanentDeInstallTemplate t WITH (NOLOCK)
join sys.columns ss on ss.object_id=object_id('TblBulkload_PermanentDeInstallTemplate')
and ss.system_type_id in (35,99,167,175,239)
WHERE
1=(case when SQL_VARIANT_PROPERTY(t.IDCOL, 'BaseType') not in ('varchar','nvarchar','char') then 1 else 0 end)
AND t.IDCOL IS NOT NULL AND t.BulkLoadId=445
i want the t.IDCOL should not come into picture.
also there more than lakhs of records are getting generated from the above query.
i have 25000 rows in the above table.
and each row consists of 12 columns , so the total count becomes 300000.
but all are not having integer datatype.
please anyone help me in resolving this.
What exactly is your generated statement supposed to do? Can you post up some sample data, also the results you expect to see from the sample data.
The list - ss.system_type_id in (35,99,167,175,239) - omits NVARCHAR.

Hello All,
I have changed my query as below, and so for it's working for me.
Set @QueryDataType = 'INSERT INTO TblBulkloadValidationLog ([BulkloadId],[ValidationMessage],[ISError])
                        SELECT {BulkLoadId}
                            ,''Invalid data type for column {ColumnName}: ''+ cast({ColumnName} as varchar)+ ''; Expected Data Type:{ColumnDataType}''
                            ,''1''
                        FROM {TableName} WITH (NOLOCK)

                            WHERE
                          1=(case when SQL_VARIANT_PROPERTY({ColumnName}, ''BaseType'') in (''varchar'',''nvarchar'',''char'') then 0 else 1 end)
                          AND {ColumnName} IS NOT NULL AND BulkLoadId={BulkLoadId}
                             AND 1=(case when (SELECT DATA_TYPE FROM
                                        INFORMATION_SCHEMA.COLUMNS
                      WHERE
                      TABLE_NAME = ''{TableName}''
                          AND
             COLUMN_NAME = ''{ColumnName}'') in (''varchar'',''nvarchar'',''char'') then 1 else 0 end) ';
            SET @QueryDataType =REPLACE(@QueryDataType , '{BulkLoadId}', @BulkLoadId);
            SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnName}', @ColumnName);
            SET @QueryDataType =REPLACE(@QueryDataType , '{TableName}', @TableName);
            SET @QueryDataType =REPLACE(@QueryDataType , '{ColumnDataType}', @ColumnDataType);

PRINT @QueryDataType