Hi, everyone. Hope all is well, I need a help in SQL Server, please. I have a ta

Question

Hi, everyone.

Hope all is well, I need a help in SQL Server, please.

I have a table called Import which has following columns

[Id]
,[RowId]
,[Code]
,[Status]
,[ASCGroup]
,[Description]
,[APCGroup]
,[ASCPaymentAmount]
,[TotalUnadjustedPayment]
,[NationalUnadjustedCopayment]
,[MinimumUnadjustedCopayment]
,[ASCPayment]
,[RelWgt]
,[YearFrom]
,[YearTo]

and I have a stored procedure which INSERT a data from table Import to second table Which called ImportFinal.

but when I ran a program I am getting this error (Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=...)

is any body can help me for this issue please.

This is my stored procedure:

CREATE PROCEDURE [dbo].[ImportAPCs]

@YearFrom DATE ,

@YearTo DATE

AS

BEGIN TRY

Begin Transaction

if not exists (Select YearFrom, YearTo From _APC where YearFrom = @YearFrom and YearTo = @YearTo)

begin

Insert into ImportFinal

(

[Id] ,

[Code] ,

[Status] ,

[ASCGroup] ,

[Description] ,

[APCGroup] ,

[ASCPaymentAmount] ,

[TotalUnadjustedPayment] ,

[NationalUnadjustedCopayment] ,

[MinimumUnadjustedCopayment] ,

[ASCPayment] ,

[RelWgt] ,

[YearFrom] ,

[YearTo]

)

values

(

(NEWID()) ,

(select Code from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select Status from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select ASCGroup from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select Description from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select APCGroup from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select ASCPaymentAmount from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select TotalUnadjustedPayment from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select NationalUnadjustedCopayment from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select MinimumUnadjustedCopayment from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select ASCPayment from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

(select RelWgt from Import where YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId) ,

@YearFrom ,

@YearTo

)

Additional note: (Year From and Year To can be duplicated but Column Id and RowId are same and is not duplicated with other rows)

Is any body can help me please?

best regards

Explanation / Answer

Hello,

One of the subqueries returning multiple values, i.e) returning more than one row.

Now the question is "how to remove this error ? ".

You have written a subquery for each column value which is unnecessary.Try to use the following code which simplifies the execution and also avoids the error because it inserts the bulk of rows at a time.

Try to rewrite the code as follows

BEGIN

Insert into ImportFinal( [Id] , [Code] , [Status] , [ASCGroup] , [Description] , [APCGroup] , [ASCPaymentAmount] , [TotalUnadjustedPayment] , [NationalUnadjustedCopayment] , [MinimumUnadjustedCopayment] , [ASCPayment], [RelWgt] , [YearFrom] , [YearTo])

Values SELECT

[Id] , [Code] , [Status] , [ASCGroup] , [Description] , [APCGroup] , [ASCPaymentAmount] , [TotalUnadjustedPayment] , [NationalUnadjustedCopayment] , [MinimumUnadjustedCopayment] , [ASCPayment], [RelWgt] , [YearFrom] , [YearTo]

FROM Import

WHERE YearFrom = @YearFrom and YearTo = @YearTo and Id = RowId)

END

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.