find and solve || findandsolve.com
Please wait.....

SQL: Msg 116,Only one Expression can be Specified in the Select List when the Subquery is not Introduced with EXISTS.

SQL: Msg 116,Only one Expression can be Specified in the Select List when the Subquery is not Introduced with EXISTS.

Whe I have create SP in sql the some erro has been occured as given below

sg 116, Level 16, State 1, Procedure TrailBalance.ReportV1, Line 41

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


CREATE PROCEDURE [dbo].[TrailBalance.ReportV1]
(
 @SchoolCode NVARCHAR(100)
 )
 AS
 BEGIN
 DECLARE @Assets NVARCHAR(MAX);
 SET @Assets=(
       SELECT 
     L.LedgerName
    ,L.Code
    ,(
    (SELECT SUM(ISNULL(DrAmount,0))FROM AccountSummary
     WHERE [email protected]
     AND VoidDate IS NULL
     AND AccountMode='Cr'
     AND LedgerId = A.LedgerId)-
     (SELECT SUM(ISNULL(CrAmount,0))FROM AccountSummary
     WHERE [email protected]
     AND VoidDate IS NULL
     AND AccountMode='Cr'
     AND LedgerId = A.LedgerId)
     ) AS DrAmount
    FROM AccountLedger AS L
    INNER JOIN AccountSummary AS A
    ON A.LedgerId = L.LedgerId
    WHERE L.MainGroupId= 1
    
 )
 END

Answer

Options : 1

Sub query is not return value whe we are not return json path.So add the section under where condition as give below

 FOR JSON PATH,ROOT('AssetList'));

After the the  sql script code as given below

ALTER PROCEDURE [dbo].[TrailBalance.ReportV1]
(  @SchoolCode NVARCHAR(100)  )
AS
 BEGIN
 DECLARE @Assets NVARCHAR(MAX);
 SET @Assets=(         SELECT      L.LedgerName     ,L.Code     ,(
    SELECT SUM(ISNULL(D.DrAmount,0))FROM AccountSummary AS D
    WHERE [email protected]
    AND D.VoidDate IS NULL
   AND D.AccountMode='Dr'    AND D.LedgerId = A.LedgerId)   ) AS DrAmount
    FROM AccountLedger AS L     INNER JOIN AccountSummary AS A     ON A.LedgerId = L.LedgerId     WHERE L.MainGroupId= 1     AND [email protected]     FOR JSON PATH,ROOT('AssetList'));
END

Options: 2

https://stackoverflow.com/questions/14080428/sql-msg-116-level-16-state-1-line-1-only-one-expression-can-be-specified-in

https://www.sqlservercentral.com/forums/topic/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not-introduced-with-exists-4

http://www.sql-server-helper.com/error-messages/msg-116.aspx


Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

Comments



Report Response