Good day! I have a procedure:

USE [Fstp] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE Report @StartDate DateTime, @EndDate DateTime, @client nvarchar(100) = '' AS BEGIN SET NOCOUNT ON; SELECT (case when oa.id is not null then (case when c.CLIENTS_CLASS='cl_privs' then cp.fio when c.CLIENTS_CLASS='cl_ent' then ce.jur_name end) else (case when mst_c.CLIENTS_CLASS='cl_privs' then case when ma.role_participant_type=2 then mst_cip.name else mst_cp.fio end when mst_c.CLIENTS_CLASS='cl_ent' then mst_ce.jur_name end) end) as 'Страхователь', ISNULL(convert(Varchar(10),oa.policy_date,104), convert(Varchar(10),ma.PolicyDate,104)) as policy_date FROM dbo.DOCS d inner JOIN ogpo_application oa ON oa.docs_id = d.DOCS_ID inner join docs_policy dp on dp.docs_id=d.DOCS_ID left join clients c on oa.insurer_id=c.CLIENTS_ID left join cl_privs cp on c.CLIENTS_ID=cp.CL_PRIVS_ID left join cl_ent ce on c.CLIENTS_ID=ce.CL_ENT_ID LEFT JOIN mst_application ma ON ma.docs_id = d.DOCS_ID left join mst_docs_policy mdp on mdp.docs_id=d.DOCS_ID left join clients mst_c on ma.insurer_id=mst_c.CLIENTS_ID left join cl_privs mst_cp on mst_c.CLIENTS_ID=mst_cp.CL_PRIVS_ID left join client_IPKH mst_cip on mst_cip.clients_id=mst_c.CLIENTS_ID and mst_cip.is_history=0 left join cl_ent mst_ce on mst_c.CLIENTS_ID=mst_ce.CL_ENT_ID Where (policy_date between @StartDate AND @EndDate) and ('Страхователь' = '' or 'Страхователь' = @client)

I need to search for policy_date and 'Страхователь' . This query does not search. 'Страхователь' = @client seems so wrong to me. How in case of a case of a construction to equate a parameter?

  • @ Zufir, there is no "Insured" field in the table. The insurer is taken from the clients (clients_id, ...), cl_privs (FIO, Cl-privs_id) tables and from several tables - Zhandos
  • In this case, the insured - a pseudonym - Zhandos
  • Yes, already understood, corrected. - Zufir

3 answers 3

WHERE parsed to SELECT , so that it cannot access columns by aliases. There are two options:

To wrap in one more select:

 SELECT * FROM (ваш SELECT без WHERE) restable WHERE (policy_date between @StartDate AND @EndDate) and ([Страхователь] = '' or [Страхователь] = @client)

Use CTE:

 ;WITH restable as (ваш SELECT без WHERE) SELECT * FROM restable WHERE (policy_date between @StartDate AND @EndDate) and ([Страхователь] = '' or [Страхователь] = @client)

Both options from the point of view of SQL Server are equivalent to substitution of the whole CASE in WHERE

    1. Do not use Russian field names.
    2. If you have already used them, [Страхователь] = '' or [Страхователь] = @client them not in quotation marks, but in square brackets: as [Страхователь] , [Страхователь] = '' or [Страхователь] = @client

    3. Wrap the case in outer apply and refer to it in the condition. Somehow:

       SELECT insurInfo.Name, insurInfo.policy_date FROM dbo.DOCS d inner JOIN ogpo_application oa ON oa.docs_id = d.DOCS_ID inner join docs_policy dp on dp.docs_id=d.DOCS_ID left join clients c on oa.insurer_id=c.CLIENTS_ID left join cl_privs cp on c.CLIENTS_ID=cp.CL_PRIVS_ID left join cl_ent ce on c.CLIENTS_ID=ce.CL_ENT_ID LEFT JOIN mst_application ma ON ma.docs_id = d.DOCS_ID left join mst_docs_policy mdp on mdp.docs_id=d.DOCS_ID left join clients mst_c on ma.insurer_id=mst_c.CLIENTS_ID left join cl_privs mst_cp on mst_c.CLIENTS_ID=mst_cp.CL_PRIVS_ID left join client_IPKH mst_cip on mst_cip.clients_id=mst_c.CLIENTS_ID and mst_cip.is_history=0 left join cl_ent mst_ce on mst_c.CLIENTS_ID=mst_ce.CL_ENT_ID outer apply (select (case when oa.id is not null then (case when c.CLIENTS_CLASS='cl_privs' then cp.fio when c.CLIENTS_CLASS='cl_ent' then ce.jur_name end) else (case when mst_c.CLIENTS_CLASS='cl_privs' then case when ma.role_participant_type=2 then mst_cip.name else mst_cp.fio end when mst_c.CLIENTS_CLASS='cl_ent' then mst_ce.jur_name end) end) as Name, ISNULL(convert(Varchar(10),oa.policy_date,104), convert(Varchar(10),ma.PolicyDate,104)) as policy_date) InsurInfo Where (InsurInfo.policy_date between @StartDate AND @EndDate) and (InsurInfo.Name = '' or InsurInfo.Name = @client)
    • I do not have a field in the table. The policyholder is a pseudonym - Zhandos
    • and how is policy_date? - Zhandos
    • put it in the same InsurInfo and contact insurInfo.policy_date. corrected the answer. - Zufir

    As Zuffir wrote, it is better not to use Russian field names and aliases. But in general, you can leave as is, replacing only on the condition

     ([Страхователь] = '' or [Страхователь] = @client)

    Or even on

     (Страхователь = '' or Страхователь = @client)

    But in general, I recommend reviewing some requests, since it is better to filter by the main columns of the table, rather than by the results of subqueries. Otherwise there will be performance problems.

    UPD Once this is not a subquery, you can duplicate the logic of a computable field. Something will turn out like:

     (oa.id is not null and ((c.CLIENTS_CLASS='cl_privs' and cp.fio = @client) or (c.CLIENTS_CLASS='cl_ent' and ce.jur_name = @client))) or (oa.id is null and ((mst_c.CLIENTS_CLASS='cl_privs' and ((ma.role_participant_type<>2 and mst_cp.fio = @client) or (ma.role_participant_type=2 and mst_cip.name = @client))) or (mst_c.CLIENTS_CLASS='cl_ent' and mst_ce.jur_name = @client)))
    • in WHERE it is impossible to refer to the columns by aliases - this is the essence of the question - PashaPash ♦
    • Hmm, yes. Something I decided was an attached request. - Aries