😺
wiki
  • Welcome, Internet Strangers!
  • sql
    • etl
      • Basics to Remember
      • Context to Remember
      • Naming Practices
      • ETL Steps
    • performance
      • Please No
      • Initial Explorations
      • Stored Procedures
    • select-tricks
      • Over Partition
      • Stored Procedures*
      • Creating Parameters
  • python
    • Working with Files
    • Classes in Python
    • Dictionaries
    • Working with Strings
    • Using Lambda
    • Seaborn
    • machine-learning
      • Learning Pandas
      • MatPlotLib! The Dreaded Line Graph...
      • matlab-qualgraphs-notes
      • Linear Regression Example
      • kNN Analysis in ScikitLearn
    • Neat Snippets
  • bash
    • helpful_cmd
  • math
    • Basic Definitions
    • Linear Regressions
    • Meaningful Sampling
Powered by GitBook
On this page

Was this helpful?

  1. sql
  2. select-tricks

Creating Parameters

The way to adjust queries quickly

PreviousStored Procedures*Nextpython

Last updated 3 years ago

Was this helpful?

Creating a filters table within a query can be a huge timesaver to quickly adjust or reuse parameters.

For example:

WITH filters AS (
    SELECT 
        CURRENT_DATE as end_date
        ,42 AS cus_id
    )
SELECT col1, col2, col3
FROM table1
WHERE      table1.datetime < (SELECT end_date FROM filters)
    AND    table1.cus_id == (SELECT cus_id FROM filters)

Not only can this quickly be adjusted to take a new end_date, but the top three lines can then be copy pasted into other queries with the same WHERE clauses. This requires some query standardization, but can lend speed for initial investigations of a known dataset.

Note, this can work differently depending on the dialect of SQL used, so check your documentation. As well, this can make queries more susceptible to if outward facing.

Nonetheless, this is fairly universal and very useful to be able to quickly run a query, then rerun with slightly different parameters.

injection attacks