Can I use a variable inside cursor declaration?-CodePudding

Is this code valid?

-- Zadavatel Login ID
DECLARE @ZadavatelLoginId nvarchar(max) =
            (SELECT TOP 1 LoginId 
             FROM
                 (SELECT Z.LoginId, z.Prijmeni, k.spojeni 
                  FROM TabCisZam Z 
                  LEFT JOIN TabKontakty K ON Z.ID = K.IDCisZam 
                  WHERE druh IN (6,10)) t1
             LEFT JOIN
                 (SELECT ko.Prijmeni, k.spojeni, ko.Cislo 
                  FROM TabCisKOs KO 
                  LEFT JOIN TabKontakty K ON K.IDCisKOs = KO.id 
                  WHERE druh IN (6, 10)) t2 ON t1.spojeni = t2.spojeni 
                                            AND t1.Prijmeni = t2.Prijmeni
             WHERE 
                 t2.Cislo = (SELECT CisloKontOsoba 
                             FROM TabKontaktJednani 
                             WHERE id = @IdKJ))
        
-- Pokud je řešitelský tým prázdný
IF NOT EXISTS (SELECT * FROM TabKJUcastZam WHERE IDKJ = @IdKJ)
BEGIN
    DECLARE ac_loginy CURSOR FAST_FORWARD LOCAL FOR
        -- Zadavatel
        SELECT @ZadavatelLoginId
END
ELSE BEGIN

I am trying to pass the variable @ZadavatelLoginId into the cursor declaration and SSMS keeps telling me there is a problem with the code even though it is working.

Msg 116, Level 16, State 1, Procedure et_TabKontaktJednani_ANAFRA_Tis_Notifikace, Line 575 [Batch Start Line 7]
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Can anyone help?

CodePudding user response：

How about you create a #temp table for each sub query since the problem is coming up due to the sub queries?

CREATE TABLE #TEMP1
(
    LoginID nvarchar(max)
)

CREATE TABLE #TEMP2
(
    ko.Prijmeni nvarchar(max), 
    k.spojeni nvarchar(max), 
    ko.Cislo nvarchar(max)
)

CodePudding user response：

I do not see anything in your posted query that could trigger the specific message that you listed. You might get an error if the subquery (SELECT CisloKontOsoba FROM TabKontaktJednani WHERE id = @IdKJ) returned more than one value, but that error would be a very specific "Subquery returned more than 1 value...".

However, as written, your cursor query is a single select of a scalar, which would never yield anything other than a single row.

If you need to iterate over multiple user IDs, but wish to separate your selection query from your cursor definition, what you likely need is a table variable than can hold multiple user IDs instead of a scalar variable.

Something like:

DECLARE @ZadavatelLoginIds TABLE (LoginId nvarchar(max))
INSERT @ZadavatelLoginIds
SELECT t1.LoginId
FROM ...

DECLARE ac_loginy CURSOR FAST_FORWARD LOCAL FOR
    SELECT LoginId
    FROM @ZadavatelLoginIds
OPEN ac_loginy
DECLARE @LoginId nvarchar(max)

FETCH NEXT FROM ac_loginy INTO @LoginId
WHILE @@FETCH_STATUS = 0
BEGIN
    ... Send email to @LoginId ...

    FETCH NEXT FROM ac_loginy INTO @LoginId
END

CLOSE ac_loginy
DEALLOCATE ac_loginy

A #Temp table can also be used in place of the table variable with the same results, but the table variable is often more convenient to use.

As others have mentioned, I believe that your login selection query is overly complex. Although this was not the focus of your question, I still suggest that you attempt to simplify it.

An alternative might be something like:

SELECT Z.LoginId 
FROM TabKontaktJednani KJ
JOIN TabCisKOs KO ON KO.Cislo = KJ.CisloKontOsoba
JOIN TabCisZam Z ON Z.Prijmeni = KO.Prijmeni
JOIN TabKontakty K ON K.IDCisZam = Z.ID
WHERE KJ.id = @IdKJ
AND K.druh IN (6,10)

The above is my attempt to rewrite your posted query after tracing the relationships. I did not see any LEFT JOINS that were not superseded by other conditions that forced them into effectively being inner joins, so the above uses inner joins for everything. I have assumed that the druh column is in the TabKontakty table. Otherwise I see no need for that table. I do not guarantee that my re-interpretation is correct though.