473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

While Loop

10
Hi All

I need some help again. I am using a instead of insert to update and insert records. When I am doing multiple records i get am error

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <=, <, >= or when the subquery is used as an
expression.

So i am thinking of using a cursor to do this.
Am i right in wanting to use a cursor or is there some other way in achieving this

I was using a table variable in the stored procedure

the insert statement in the store procedure looks like this

INSERT Customer
Select * FROM @Table
this then fires the instead on insert trigger

thanks in advance
Mar 20 '08 #1
2 1814
ck9663
2,878 Expert 2GB
Hi All

I need some help again. I am using a instead of insert to update and insert records. When I am doing multiple records i get am error

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <=, <, >= or when the subquery is used as an
expression.

So i am thinking of using a cursor to do this.
Am i right in wanting to use a cursor or is there some other way in achieving this

I was using a table variable in the stored procedure

the insert statement in the store procedure looks like this

INSERT Customer
Select * FROM @Table
this then fires the instead on insert trigger

thanks in advance

The error you're getting came from one of your subquery. This happens when you're expecting a subquery to return a single value but instead returns two or more. Since you did not post your code, I can't say if it's inside the trigger or outside.

-- CK
Mar 20 '08 #2
allik7
10
This is the code in the instead of insert trigger

ALTER TRIGGER [Cust_Audit] ON [dbo].[Customer]
INSTEAD OF INSERT
AS

DECLARE @CustNum int
DECLARE @CustomerCode INT,@CustomerIdentificationType INT
DECLARE @CustomerIdentificationGroupCode CHAR(50),@DocumentNumber CHAR(50)
DECLARE @FirstName CHAR(50),@LastName CHAR(50),@MiddleName CHAR(50)
DECLARE @CustomerName CHAR(50)
BEGIN
SET @CustomerCode = (select customercode from inserted)
SET @CustomerIdentificationType = (select CustomerIdentificationType from inserted)
SET @CustomerIdentificationGroupCode = (select CustomerIdentificationGroupCode from inserted)
SET @DocumentNumber = (select DocumentNumber from inserted)
SET @FirstName = (select FirstName from inserted)
SET @LastName = (select LastName from inserted)
SET @MiddleName = (select MiddleName from inserted)
SET @CustomerName =(select CustomerName from inserted)
END
Set @CustNum =(Select top 1 cc.CustomerCode From Customer cc, inserted i WHERE cc.CustomerCode = i.CustomerCode)


--set nocount off

If ISNULL(@CustNum,'')=''

BEGIN
INSERT INTO CUSTOMER(CustomerCode,CustomerIdentificationType ,
CustomerIdentificationGroupCode,DocumentNumber,
FirstName,LastName,MiddleName,
CustomerName)
values(@CustomerCode,@CustomerIdentificationType,@ CustomerIdentificationGroupCode,@DocumentNumber,
@FirstName,@LastName,@MiddleName,@CustomerName)
end

SELECT CustomerCode FROM Customer where CustomerCode = @CustomerCode
INSERT INTO
SysDatabase_Transactions( TableName,Operation,TransferDateTime)
Select b.CustomerCode,'Customer','I',Getdate()
From Inserted b
--END
ELSE
BEGIN
UPDATE Customer
SET CustomerCode = @CustomerCode,CustomerIdentificationType = @CustomerIdentificationType,
CustomerIdentificationGroupCode = @CustomerIdentificationGroupCode,
DocumentNumber = @DocumentNumber,FirstName=@FirstName,LastName=@Las tName,
MiddleName=@MiddleName,CustomerName=@CustomerName WHERE CustomerCode = @CustomerCode
--END

SELECT CustomerCode FROM Customer where CustomerCode = @CustNum
IF @@ROWCOUNT <> 0
INSERT INTO
SysDatabase_Transactions( Record_Id,TableName,Operation,TransferDateTime)
Select b.CustomerCode,'Customer','U',Getdate()
From Inserted b
END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Mar 21 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: jsaul | last post by:
Hi there, wouldn't it be useful to have a 'while' conditional in addition to 'if' in list comprehensions? foo = for i in bar: if len(i) == 0: break foo.append(i)
33
by: Diez B. Roggisch | last post by:
Hi, today I rummaged through the language spec to see whats in the for ... else: for me. I was sort of disappointed to learn that the else clauses simply gets executed after the loop-body -...
9
by: Ben | last post by:
I have two 'Do While Not' statements, that are getting information from the same recordset. If I comment out the first one I can get the results for the second one, and vice-versa. Why is this...
7
by: Mahesh Kumar Reddy.R | last post by:
Hi Can any body resolve this.. In what cases one of the loop constructs better than other interms of speed , space and any other (redability). thanks mahesh
36
by: invni | last post by:
I have a nested while. How do I go from the inner while to the beginning of the outer while? Can this be done without using goto? while_1() { some codes here while_2() { if true go to the...
6
by: John Pass | last post by:
What is the difference between a While and Do While/Loop repetition structure. If they is no difference (as it seems) why do both exist?
12
by: Howard | last post by:
Hello everyone (total VB.NET beginner here), I'm reading the "SAMS Teach Yourself VB.NET In 21 Days" book, and came across an exercise that I can't get to work. The exercise asks that you create...
16
by: Claudio Grondi | last post by:
Sometimes it is known in advance, that the time spent in a loop will be in order of minutes or even hours, so it makes sense to optimize each element in the loop to make it run faster. One of...
10
by: rohitjogya | last post by:
Can anyone tell me the difference bet for loop and while loop execution? ____________________ for (i=0 ; i<10 ; i++) ; /* Do nothing*/ print i; ___________________ i=0;
5
by: Alex | last post by:
Hi I just want to clear something up in my head with while loops and exceptions. I'm sure this will probably be a no brainer for most. Check this simple pseudo-code out (vb.net): ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.