Skip to content
TheChatArchiveThe Chat Archive
Amazon Novaamazon.nova-pro-v1:0· AmazonHugging Face LMArena Human Preference 140K

I have the following code, to generate and execute SQL based on a given set of…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Metallama-4-maverick-17b-128e-instruct’s answer.
  1. User

    I have the following code, to generate and execute SQL based on a given set of variables. The code exists as follows:

    --Dynamic Velcro DECLARE @TableName NVARCHAR(128) = 'table_one'; DECLARE @SchemaName NVARCHAR(128) = 'dbo'; DECLARE @SearchValue NVARCHAR(100) = 'squirrel'; DECLARE @SQL NVARCHAR(MAX) = '';

    SELECT @SQL = STRING_AGG( CAST(QUOTENAME(c.name) + ' LIKE ''%' + @SearchValue + '%''' AS NVARCHAR(MAX)), -- Cast to NVARCHAR(MAX) here ' OR ' ) FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.name = @TableName AND s.name = @SchemaName AND c.system_type_id IN (167, 175, 231, 239); -- char, varchar, nchar, nvarchar

    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + @SQL;

    -- Print or execute the dynamic SQL PRINT @SQL; --EXEC sp_executesql @SQL;

    Now, I would like you to modify the existing code to accept a multi-term SearchValue (suppose I wanted any columns that contained squirrel, fox, rabbit, etc).

  2. Amazon Nova Assistant

    Certainly! To modify your code to accept a multi-term SearchValue, you can split the search value into individual terms and then construct the dynamic SQL to search for any of these terms in the specified columns. Here's how you can do it:

    1. Split the SearchValue into individual terms.
    2. Construct the dynamic SQL to include all terms using the LIKE operator.

    Here's the modified code:

    --Dynamic Velcro
    DECLARE @TableName NVARCHAR(128) = 'table_one';
    DECLARE @SchemaName NVARCHAR(128) = 'dbo';
    DECLARE @SearchValue NVARCHAR(MAX) = 'squirrel,fox,rabbit'; -- Comma-separated values
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @SearchTerms TABLE (SearchTerm NVARCHAR(100));
    
    -- Split the SearchValue into individual terms
    INSERT INTO @SearchTerms (SearchTerm)
    SELECT value
    FROM STRING_SPLIT(@SearchValue, ',');
    
    -- Construct the dynamic SQL
    SELECT @SQL = STRING_AGG(
        CAST(
            QUOTENAME(c.name) + ' LIKE ''%' + st.SearchTerm + '%'''
            AS NVARCHAR(MAX)
        ),
        ' OR '
    )
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    CROSS JOIN @SearchTerms st
    WHERE t.name = @TableName
      AND s.name = @SchemaName
      AND c.system_type_id IN (167, 175, 231, 239); -- char, varchar, nchar, nvarchar
    
    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
               ' WHERE ' + @SQL;
    
    -- Print or execute the dynamic SQL
    PRINT @SQL;
    --EXEC sp_executesql @SQL;
    

    Explanation:

    1. Splitting the Search Value:

      • The STRING_SPLIT function is used to split the comma-separated @SearchValue into individual terms and insert them into a table variable @SearchTerms.
    2. Constructing the Dynamic SQL:

      • The dynamic SQL is constructed by joining the @SearchTerms table with the columns of the specified table. This ensures that each column is checked against all search terms.
    3. Executing the Dynamic SQL:

      • The constructed SQL query is printed (or can be executed using EXEC sp_executesql @SQL).

    This approach allows you to search for multiple terms in any of the specified columns dynamically.

  3. User

    Suppose I can't create a table. How would I approach this?

  4. Amazon Nova Assistant

    If you can't create a temporary table to store the split search terms, you can use a different approach by dynamically constructing the LIKE conditions for each search term within the dynamic SQL itself. Here’s how you can do it:

    --Dynamic Velcro
    DECLARE @TableName NVARCHAR(128) = 'table_one';
    DECLARE @SchemaName NVARCHAR(128) = 'dbo';
    DECLARE @SearchValue NVARCHAR(MAX) = 'squirrel,fox,rabbit'; -- Comma-separated values
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @SearchTerms NVARCHAR(MAX) = '';
    
    -- Split the SearchValue into individual terms and construct the LIKE conditions
    SELECT @SearchTerms += 
        IIF(@SearchTerms = '', '', ' OR ') + 
        QUOTENAME(c.name) + ' LIKE ''%' + value + '%'''
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    CROSS APPLY STRING_SPLIT(@SearchValue, ',') 
    WHERE t.name = @TableName
      AND s.name = @SchemaName
      AND c.system_type_id IN (167, 175, 231, 239); -- char, varchar, nchar, nvarchar
    
    -- Construct the final dynamic SQL
    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
               ' WHERE ' + @SearchTerms;
    
    -- Print or execute the dynamic SQL
    PRINT @SQL;
    --EXEC sp_executesql @SQL;
    

    Explanation:

    1. Splitting the Search Value:

      • The STRING_SPLIT function is used within the SELECT statement to split the comma-separated @SearchValue into individual terms.
    2. Constructing the LIKE Conditions:

      • The IIF function is used to add an OR between each LIKE condition only if it’s not the first term.
      • The QUOTENAME function ensures that column names are properly quoted.
    3. Building the Dynamic SQL:

      • The dynamic SQL is constructed by concatenating all the LIKE conditions into the @SearchTerms variable.
    4. Executing the Dynamic SQL:

      • The constructed SQL query is printed (or can be executed using EXEC sp_executesql @SQL).

    This approach avoids the need to create a temporary table and instead builds the conditions directly within the dynamic SQL string.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.