Post reply

Variables as field names

  • January 25, 2019 at 10:47 am

    #409463

    Hi, I have written the below (this is an extract of a larger script)

    and I was hoping to pass the variable @valicolname to the select statement so I got a dynamic field name in my SQL.

    Can anyone assist in getting it working please?
    the bottom case when returns FALSE 100% of the time.

    Cheers,
    Dave


    Declare @valicolName varchar(max)
    Declare @Value varchar(2)

    set @value = 'C'

     set @valiColName = '';

        set @valiColName = (SELECT
        [Validates_From]
        FROM [002_CGDS_VALIDATION].[dbo].[002_Vali_Validation_Lists] where Column_Name = @colName)

    CASE WHEN @VALUE IN (SELECT DISTINCT  @valiColName  as

              FROM DIM_Product) THEN
               'PASS' else 'FAIL' END as Validation_Result,

    1. January 25, 2019 at 2:00 pm

      #2020166

      The select in your CASE is just selecting @valicolName as text. As a result, SELECT DISTINCT @valiColName as

        FROM DIM_Product 
        is functionally the same as: SELECT @valiColName as

           
          AFAIK you'll need to use a dynamic select, something like:DECLARE @sql NVARCHAR(4000)
          SET @sql = 'SELECT DISTINCT' + @valiColName + ' as

            FROM DIM_Product'
            EXEC sp_executesql @sql             And of course make sure to sanitize/parameterize the inputs to avoid injection.  The sample above is oversimplified to give the gist of the idea.

            .

            .

          1. January 25, 2019 at 2:08 pm

            #2020167

            Hi thanks for this. I will give it a go.

            Cheers,

            Dsve

          2. January 28, 2019 at 9:41 am

            #2020302

            Variables can't be used as columns. You can use dynamic SQL as noted to get a different column returned.

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

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