1

Topic: Different variants of parameters

Hello!
Let there is such still a crude SQL query. It is necessary to preempt the data depending on what are set parameters. For example, It is necessary to preempt on what that to cities, on what that to clients,  to persons, on a TT. Considering that we set, for example, the certain client, and remaining fields-parameters are not set. Still the combination, for example, Cities + Clients, Jur + a TT, etc. Arises a question how to write request so that was general-purpose for the job of different parameters.
[spoiler a SQL query]

Declare @ varchar (8)
Declare @ varchar (8)
Declare @ varchar (8)
Declare @ varchar (8)
SELECT
[City];
[Jur the person];
[Client];
[The TT code];
[Shipments ];
[Resets ];
[Implementation ]
FROM
[Database]
WHERE
[City] = @
AND
[Jur the person] = @
AND
[Client] = @
AND
[The TT code] = @

[/spoiler]

2

Re: Different variants of parameters

ferzmikk;

[City] =isnull (@, [the City])

3

Re: Different variants of parameters

FAQ: Variable number of criteria of selection in request

4

Re: Different variants of parameters

Kopelly wrote:

ferzmikk;

[City] =isnull (@, [the City])

This case is characteristic, if we set one city, and if some cities?

5

Re: Different variants of parameters

ferzmikk wrote:

and if some cities?

Looking as it to make... Most likely, it is necessary to use function of search of substring (or ).

6

Re: Different variants of parameters

Akina wrote:

it is passed...
Looking as it to make... Most likely, it is necessary to use function of search of substring (or ).

Let's assume, @ = ' Gorod1, Gorod2 '. Or transmission to parameter in the form of an array, whether though I do not know probably so in SQL.

7

Re: Different variants of parameters

ferzmikk wrote:

Hello!
... That was general-purpose for the job of different parameters.
[/code] [/spoiler]

Dynamic collect request listing, behind that Exec ()

8

Re: Different variants of parameters

.....
where ((@ is null) or ([City] = @))
or ((@ is null) or ([Legal person] = @))
or ((@ is null) or ([Client] = @))

Keep in mind that for those who writes variables or columns cyrillic in a hell the separate are prepared

9

Re: Different variants of parameters

.))

10

Re: Different variants of parameters

The pancake, was mistaken. In request it is necessary "AND" to put

.....
where ((@ is null) or ([City] = @))
AND ((@ is null) or ([Legal person] = @))
AND ((@ is null) or ([Client] = @))

11

Re: Different variants of parameters

ferzmikk wrote:

it is passed...
This case is characteristic, if we set one city, and if some cities?

What question (example) - such answer...
You assort @ on separate values...

12

Re: Different variants of parameters

ferzmikk wrote:

It is necessary to preempt on what that to cities.
And if some cities?
Let's assume, @ = ' Gorod1, Gorod2 '.

and you ask this question to that who such basis designed.

13

Re: Different variants of parameters

ferzmikk wrote:

we Assume, @ = ' Gorod1, Gorod2 '

(@ IS NULL OR ', ' + [the City] + ', ' LIKE ' %, ' + @ + ', % ')
AND (@ IS NULL OR ', ' + [the Legal person] + ', ' LIKE ' %, ' + @ + ', % ')
......................

14

Re: Different variants of parameters

And if in the form of an array to transfer, SQL can recognize?

15

Re: Different variants of parameters

xml transfer, in  it assort as you it is necessary

16

Re: Different variants of parameters

Konst_One wrote:

xml transfer, in  it assort as you it is necessary

That means ?

17

Re: Different variants of parameters

ferzmikk;
Stored procedure

18

Re: Different variants of parameters

Stored procedure Stored Procedure

19

Re: Different variants of parameters

In SSRS in such cases it is used IN.

(@ is null) or ([the City] IN @)

20

Re: Different variants of parameters

bideveloper wrote:

In SSRS in such cases it is used IN.

(@ is null) or ([the City] IN @)

And it here at what?

21

Re: Different variants of parameters

iap wrote:

And it here at what?

ferzmikk wrote:

There is a question how to write request so that was general-purpose for the job of different parameters.

So the request when the parameter is not specified, specified with one value works, is specified with set of values.

22

Re: Different variants of parameters

bideveloper;
To you speak that in TSQL it will not work, and that that is in SSRS remains in the same place smile

23

Re: Different variants of parameters

TaPaK;
SSRS the request about the server finally sends in the form of T-SQL.
If instead of @ to substitute a subquery under the table of values of parameter, unless will not work?

24

Re: Different variants of parameters

bideveloper wrote:

TaPaK;
SSRS the request about the server finally sends in the form of T-SQL.
If instead of @ to substitute a subquery under the table of values of parameter, unless will not work?

It will be finite, but  it such operation it is necessary

25

Re: Different variants of parameters

Optimally - to write application which will be  requests.
The same CLR factory, for example.