Using Dynamic SQL to create a list

  • david_h_edmonds

    Ten Centuries

    Points: 1232

    Hi, the brief is to:
    use sql to pull a distinct list of values together and then to validate a set of data against the list.
    SQL
    SELECT  STUFF(( SELECT ',''' + Booking_Type_Code_CTM + CHAR(39) 
    FROM DIM_Product  FOR  XML PATH('')  ), 1, 1, '')  AS List

    this gives me:
    'C','E','F','GC','H','HX','I','K','P','Q','R','T','V','Z'
    which I assign to the variable @valivalue

    I then say:

    case when @valiValue is null then 'N/A' else

    CASE WHEN rtrim(@VALUE) IN (@valiValue) THEN

    'PASS' else 'FAIL' END END as Validation_Result,

    This works in so far as the list gets passed to the variable but using that variable as part of in(@valivalue) doesn't work and simply returns FAIL 100% of the time.
    Can anyone give me any advice please?

    Cheers,
    Dave

  • Jonathan AC Roberts

    SSCoach

    Points: 16793

    case when @valiValue is null then 'N/A' else

             CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN

             'PASS' else 'FAIL' END END as Validation_Result,

  • david_h_edmonds

    Ten Centuries

    Points: 1232

    Jonathan AC Roberts - Wednesday, February 20, 2019 5:00 AM

    case when @valiValue is null then 'N/A' else

             CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN

             'PASS' else 'FAIL' END END as Validation_Result,

    I love you!!

    Perfect answer many  thanks,

    Dave

  • Jeff Moden

    SSC Guru

    Points: 994284

    I don't understand why you simply don't validate the data before you concatenate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • david_h_edmonds

    Ten Centuries

    Points: 1232

    Jeff Moden - Wednesday, February 20, 2019 7:01 AM

    I don't understand why you simply don't validate the data before you concatenate it.

    Because it's pulling a dynamic list of values from the dimension so there may be new codes added at any time therefore rather than have a list of hardcoded values to validate against, this allows for any new values to be automatically tested against.
    It is part of a much bigger validation routine that happens before the data gets anywhere near the DW.
    If you're interested in the full script,
    Feel free to critique (nicely please)
    PS it runs on 10,000 rows in about 20 seconds so processing speed is more than adequate.

    Cheers,

    Dave

    --declare variables--

    Declare @tablename varchar(1000)

    Declare @colName varchar(1000)

    Declare @order_int int

    declare @Mandatory varchar(3)

    declare @data_type varchar(50)

    declare @length int

    declare @SQL varchar(1000)

    declare @value varchar(1000)

    DECLARE @result TABLE ( Value VARCHAR (1000) )

    declare @sql_tbl table (value Varchar(max))

    declare @vali_tbl table (value varchar(max))

    declare @row_num int

    declare @row_num_var varchar(3)

    declare @total_rows int

    declare @total_rows_var varchar(max)

    declare @max_orderint int

    declare @outcome varchar(4)

    declare @valiColName varchar(max)

    declare @valiColSQL varchar(max)

    declare @valiValue varchar(max)

    --set inital varaiable values--

    set @total_rows = (select COUNT(*) from [002_CGDS_VALIDATION].dbo.[002_Vali_Data])

    set @total_rows_var = CAST(@total_rows as varchar(max))

    set @row_num = 1

    set @row_num_var = CAST(@row_num as varchar(3))

    Set @tablename = '[002_CGDS_VALIDATION].dbo.[002_Vali_Data]'

    set @max_orderint = (select COUNT(Column_name)

    from INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME in ('Fact_Corelines_Staging')

    and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID'))

    Set @order_int = 1

    --loop round columns of @tableName--

    while @order_int <= @max_orderint

    begin

    set @row_num = 1

    set @row_num_var = CAST(@row_num as varchar(3))

    Set @colName = (select Column_Name from

    (select

    COLUMN_NAME,

    ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]

    from

    INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME in ('Fact_Corelines_Staging')

    and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins

    where admins.order_ident = @order_int)

    Set @length = (SELECT Length from

    (select

    CHARACTER_MAXIMUM_LENGTH as 'LENGTH',

    ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]

    from

    INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME in ('Fact_Corelines_Staging')

    and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins

    where admins.order_ident = @order_int)

    Set @data_type = (SELECT Data_Type from

    (select

    Data_Type,

    ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]

    from

    INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME in ('Fact_Corelines_Staging')

    and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins

    where admins.order_ident = @order_int)

    set @Mandatory = (SELECT Mandatory from

    (select

    case when(is_nullable = 'NO') then 'YES' ELSE 'NO' END as 'Mandatory',

    ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]

    from

    INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME in ('Fact_Corelines_Staging')

    and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins

    where admins.order_ident = @order_int)

    -----get validation list---------------------------------------------------------------------------------

    set @valiColName = '';

    set @valiColName = (SELECT

    [Validates_From]

    FROM [002_CGDS_VALIDATION].[dbo].[002_Vali_Validation_Lists] where Column_Name = @colName)

    set @valiColSQL = (Select SQL from [002_CGDS_VALIDATION].[dbo].[002_Vali_Validation_Lists] where Column_Name = @ColName)

       

    insert into @vali_tbl

    exec sp_sqlexec @valicolSQL

    set @valiValue = '';

    set @valiValue = (SELECT TOP 1 Value FROM @vali_tbl)

    delete from @vali_tbl

    -------end get validation list ----------------------------------------------------------------------------

    --loop rows of current @colname

    While @row_num <= @total_rows

    Begin

    Set @SQL = 'Select ' + @colName +

    ' from (select ' + @colName +

    ' ,row_number() over (order by booking_type_key) AS [id]

    from ' + @tablename + ') as SQL_TBL' +

    ' where id = ' + @row_num_var

    Insert into @result

    EXEC sp_sqlexec @sql

    SET @Value='';

    SET @Value = (SELECT TOP 1 Value FROM @result)

    DELETE FROM @result

    insert into temp_results(row_num,value,Length_result, Column_Name,Validation_Result,Mandatory_Result, Type_Result)

    select @row_num,

    @value,

    case when len(( @value )) > @length

    then 'FAIL' else 'PASS' END as length_result,

    @colName,

    case when @valiValue is null then 'N/A' else

    CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN

    'PASS' else 'FAIL' END END as Validation_Result,

    case when (@value IS null and @Mandatory = 'NO') then 'PASS'

    when (@value IS not null) then 'PASS'

    when (@value IS null and @Mandatory = 'YES') then 'FAIL'

    END as Mandatory_Result,

    case when SQL_VARIANT_PROPERTY(@value, 'BaseType') = @data_type then 'PASS'

    Else 'FAIL' END as type_result

    --where

    --case when len(( @value )) > @length

    --then 'FAIL' else 'PASS' END = 'FAIL'

    Set @row_num = @row_num +1

    set @row_num_var = CAST(@row_num as varchar(3))

    END

    set @order_int = @order_int +1

    End

  • ScottPletcher

    SSC Guru

    Points: 98053

    If @value is a single value -- and it seems like it would have to be only a single value -- then wouldn't it be simpler and more efficient to just use something like this?:

    ...WHEN EXISTS(SELECT 1 FROM DIM_Product WITH (NOLOCK) /*if preferred*/ WHERE Booking_Type_Code_CTM = @Value) THEN ... ELSE ...

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 6 posts - 1 through 6 (of 6 total)

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