Clarity

Expand all | Collapse all

Dynamic SQL

  • 1.  Dynamic SQL

    Posted 08-07-2017 01:20 PM

    I know this isn't the right place for SQL help but since the finished version of this could do us all some good...

     

    I'm working on a Dynamic SQL where one part is failing. I've created a little test case below to demonstrate the fail in hopes that someone can tell me what I'm doing wrong.

     

    Here's the hard coded example:

    SELECT
    TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
    , 'osumc_est_rev_date' AS ATTRIBUTE_ID
    FROM INV_INVESTMENTS I
    JOIN ODF_CA_IDEA A ON A.ID = I.ID
    WHERE osumc_est_rev_date IS NOT NULL
    ORDER BY I.LAST_UPDATED_DATE DESC

    If you want to run it, any custom attribute should do. It's telling me the top last updated date for an Idea where a particular custom attribute was used (not null). The answer 11/7/2014 has been validated.

     

    Trying to turn this little test harness dynamic.

    DECLARE @CustAttr nvarchar(75)
    SET @CustAttr = 'osumc_est_rev_date'

    SELECT
    TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
    , @CustAttr AS ATTRIBUTE_ID
    FROM ODF_CA_IDEA A
    JOIN INV_INVESTMENTS I ON A.ID = I.ID
    WHERE @CustAttr IS NOT NULL
    ORDER BY I.LAST_UPDATED_DATE DESC

    The WHERE clause isn't working. 

     

    Ideas?



  • 2.  Re: Dynamic SQL

    Posted 08-07-2017 03:32 PM

    I've not really played with dynamic sql in SQL*Server, but in Oracle the way it works is that you have to tell the engine "here is a bit of dynamic SQL, please execute it for me" rather than just trying to sneak in a bit of dynamic code into a "normal" SQL statement.

     

    Not sure I've expressed that particularly well, but I'm not in front of a DB right now to build a working example for you, but I kinda mean that the whole statement is build up (as a big string) and then passed to the SQL engine to run. So I'm saying (badly) that how you are structuring your test harness is just fundamentally wrong - your where expression evaluates to a testing that a string variable called CustAttr is not null (which because you set it earlier, it never will be).

     

    (will add to this tomorrow unless someone else chimes in with a more lucid explanation response in the mean time!)



  • 3.  Re: Dynamic SQL

    Posted 08-07-2017 10:18 PM
    set nocount on

    declare @table table (COLUMN_NAME varchar(200))
    declare @column nvarchar(200)
    declare @count int
    declare @sq nvarchar(1) = CHAR(39)
    declare @lf nvarchar(2) = CHAR(13) + CHAR(10)
    declare @sql nvarchar(4000) = @lf

    insert into @table
    select column_name from niku.information_schema.columns c where c.table_name = 'odf_ca_idea' and c.is_nullable = 'yes'

    select @count = count(*) from @table
    while (@count > 0)
    begin

         select top 1 @column = COLUMN_NAME from @table
         
         set @sql = @sql + 'select top 1 (convert(date,i.last_updated_date,106)) as last_object_updated_date'
         set @sql = @sql + @lf  + ', ' + @sq + @column + @sq + ' as columnName'
         set @sql = @sql + @lf  + ', cast(' + @column + ' as varchar(2000)) as  attribute_id'
         set @sql = @sql + @lf  + 'from niku.odf_ca_idea a'
         set @sql = @sql + @lf  + 'inner join niku.inv_investments i on a.id = i.id'
         set @sql = @sql + @lf  + 'where ' + @column + ' IS NOT NULL'
         if (@count > 1)
         set @sql = @sql + char(10) + ' union ' + char(10)
         
         delete from @table where column_name = @column
         select @count = count(*) from @table
    end

    execute sp_executesql @sql

    Not sure if this is what you are looking for but this will give you a dynamic sql for all the top 1 for all nullable fields on a given custom object table.

     

     

    So the attribute_id field get cast into a varchar to deal with different date types and allows the union to provide a single results set.

     

    V/r,

    Gene



  • 4.  Re: Dynamic SQL

    Posted 08-08-2017 05:01 AM

    Yep that ^ is what I meant!

     

    Here is my version of Rob's "harness" then;

     

    DECLARE @CustAttr nvarchar(75)
    DECLARE @V_SQL nvarchar(4000)

    SET @CustAttr = 'osumc_est_rev_date'

    SET @V_SQL = 'SELECT ' +
    'TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE ' +
    ', ' + @CustAttr + ' AS ATTRIBUTE_ID '+
    'FROM ODF_CA_IDEA A '+
    'JOIN INV_INVESTMENTS I ON A.ID = I.ID '+
    'WHERE ' + @CustAttr + ' IS NOT NULL '+
    'ORDER BY I.LAST_UPDATED_DATE DESC'

    EXECUTE (@V_SQL)

     

    (I examined the CMN_ID_SP stored procedure to work out the SQL*Server dynamic SQL syntax - with Oracle you would "execute immediate" the string)



  • 5.  Re: Dynamic SQL

    Posted 08-10-2017 04:31 PM

    Thanks Gene, Dave.

        We're leaning a different direction now (custom object and xogging the data on the object). The data is eye opening - I think this'll be something everyone will be able to use. I'll pay back your efforts if I get to take this to completion.

     

    Thanks!