I'd like to reuse a condition in a big SQL query, is that possible?

Question

For example I need to reuse a condition like this:

e.exampleTableDate between '2018-01-01' and '2018-01-10'

In multiple places in the text. Is it possible to put this query in some kind of a placeholder in the beginning and change just that to change it everywhere.

I guess I could just replace the text everywhere, but at this point I'd like to know if this is even possible. Are there things like placeholders and variables in this context in SQL?

ADDITIONAL EXAMPLE:

select
   c.name,
   (
      select distinct id
      from x
        left join y
      where
        condition 1,
        condition 2,
        **repeating condition**
   ) [stuff 1],

   (
      select distinct id
      from x
        left join y
      where
        condition 1,
        condition 2,
        condition 3,
        **repeating condition**
   ) [stuff 2],

   (
      select distinct id
      from x
        left join y
      where
        condition 1,
        **repeating condition**
   ) [stuff 3]

from country c

order by c.name

Use [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) with parameters. — trincot, Jan 24 '18 at 12:36
@a_horse_with_no_name I don't think I know. I just write queries. Is there a way to check that from Microsoft SQL server management studio? — cybera, Jan 24 '18 at 12:37
@trincot ild_flue is correct, I don't want to reuse a statement just a string. — cybera, Jan 24 '18 at 12:39
@cybera, well, without more context we cannot really know what is best for you. Compiled statements are certainly more efficient than concatenating strings for executing a new statement. Furthermore, you would have to protect your application against SQL injection. — trincot, Jan 24 '18 at 12:40
@cybera; The only solution I'm aware of to reuse strings as commands within the SQL language would be if you constructed your SQL query as dynamic SQL...If you're using some other language to invoke the SQL statement though, there may be other options available in that language... — JohnLBevan, Jan 24 '18 at 12:49
@trincot We're not talking about an application just simple read only queries to the DB. The question is if there is a way to reuse a string in a simple way. I can just replace it, but that can potentially create a problem in some situations, so I'd prefer it if I just have it written in one place in the query. Like declaring a string variable and using the variable everywhere else. — cybera, Jan 24 '18 at 12:50
Could you give an example. Depending on your needs there are methods that use `CASE`, `APPLY`, `CTE`s, etc. Each more or less useful in different scenarios. One option could have no impact on performance in one scenario, and a massive impact in another scenario. At present the question is a bit too broad. Please show at least a representative query for your use case. In terms of macro-like-substitution, there is nothing native to SQL, the closest approach would be to write code that writes SQL and substitutes in the common code as and when necessary. — MatBailie, Jan 24 '18 at 12:55
Also, note that the ***code*** `e.exampleTableDate between '2018-01-01' and '2018-01-10'` is ***not*** `just a string` from the perspective of the SQL parser. `'2018-01-01'` is just a string. — MatBailie, Jan 24 '18 at 12:59
If the SSMS queries are in the same batch, you could use local variables for the date values. With different batches, you could use SQLCMD variables and run the query from SSMS in SQLCMD mode. Add a more complete example of your queries to your question if you need help with that. — Dan Guzman, Jan 24 '18 at 13:00
@cybera, the best solution may be that you *start using* an application environment instead of typing (copy/pasting, or loading) the SQL directly in ssms. It will give you lots of benefits, including code reuse. — trincot, Jan 24 '18 at 13:06
@trincot My brother said the same thing. I'll probably do it in the next weeks if its possible. I'm working within a bunch of restrictions. — cybera, Jan 24 '18 at 13:54

score 3 · Answer 1 · edited Jan 24 '18 at 13:00

3

Perhaps you can do what you want with a view:

create view v_e as
    select e.*
    from e
    where e.exampleTableDate between '2018-01-01' and '2018-01-10';

You can then reference the views later in the script -- or anywhere, because the view is part of the database.

e.g. See below for an example of reusing this query, whilst also adding additional (different) conditions to each use:

select exampleTableId, exampleTableDate
from v_e x
where exampleTableCol in ('abc', 'def')
and not exists 
(
    select top 1 1
    from v_e y
    where y.ParentId = x.ParentId
    and y.exampleTableDate > x.exampleTableDate
)

In addition to views you may be interested in Common Table Expressions (CTEs) or Table Value Functions, depending on context.

edited Jan 24 '18 at 13:00

JohnLBevan

22,735
13
96
178

answered Jan 24 '18 at 12:39

Gordon Linoff

1,242,037
58
646
786

This sounds a lot like the prepared statement mentioned above. It is not what I need as I'm not trying do reuse a query but just a string, which will be part of the where conditions. – cybera Jan 24 '18 at 12:41
1

FYI: If using this filter on the same table(s), you can also use a CTE in place of a view. Sadly @cybera's case neither is suitable; but hopefully useful for others hitting this same question. – JohnLBevan Jan 24 '18 at 12:47

JohnLBevan · Accepted Answer · 2018-01-24T13:07:03.977

You could do that with Dynamic SQL... however this can leave you open to SQL Injection attacks, depending on where the values used to build the reused string come from:

declare @reuseString nvarchar(max) = 'e.exampleTableDate between ''2018-01-01'' and ''2018-01-10'''
, @sql nvarchar(max)

set @sql = '
    select exampleTableId Id, exampleTableDate [Date]
    from myTable e
    where ' + @reuseString  + '
    union all
    select anotherTableId, anotherTableDate
    from myOtherTable e
    where ' + @reuseString  + '
'
exec (@sql)

set @sql = '
    select *
    from aCompletelyDifferentQuery e
    where ' + @reuseString  + '
    and xyz > 123
'
exec (@sql)

If that reusable string may be empty (i.e. causing an issue in the example above as there would then be an and without a preceding where, change the code as follows; that will work regardless of whether reuse string is populated.

declare @reuseString nvarchar(max) = 'and (e.exampleTableDate between ''2018-01-01'' and ''2018-01-10'')'
, @sql nvarchar(max)

--set @reuseString = '' --optional statement to illustrate the query working when this string's blank

set @sql = '
    select *
    from aCompletelyDifferentQuery e
    where (1=1) --ensure there's always a where, without influencing the query
    ' + @reuseString  + '
    and xyz > 123
'
exec (@sql)

If you're reusing queries (i.e. applying this same filter to the same table) there are better ways (views, CTEs, or table valued functions); see @GordonLinoff's answer for more on that.

If you're using another language to invoke the SQL statements (e.g. you've written a C# app), there are other options available within those languages too... Since you've specified SSMS in your question's tags though, I assume that's not what you're after.

I'd like to reuse a condition in a big SQL query, is that possible?

2 Answers2