SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem in Stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teamjai
Starting Member

India
42 Posts

Posted - 01/17/2013 :  22:49:27  Show Profile  Reply with Quote
I am trying to insert data from table .
I have the stored procedure definition as follows

ALTER PROCEDURE [dbo].[c_Copy_InstallationPhases_C]
-- Add the parameters for the stored procedure here
@PhaseListGuid varchar(36) = NULL,
@UserGUID int = NULL
AS
SET XACT_ABORT ON
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
DECLARE @TransactionName varchar(20)
Set @TransactionName = 'Transaction_InstallationPhases'
--Begin Transaction
BEGIN TRAN @TransactionName
--------------------Begin copy----------------------------------------

Declare @InstallationPhasesGuidNew varchar(36)
Set @InstallationPhasesGuidNew = Replace(CONVERT(varchar(255), NewID()),'-','')


Insert Into dbo.cMat_InstallationPhases_C with (ROWLOCK)
Select RSRC,Deleted, Getdate(),NUll,@UserGUID,Null,ReadAccess,WriteAccess,
@InstallationPhasesGuidNew,Name
from dbo.cMat_InstallationPhases_C with (NOLOCK) where deleted =0 and camosGUID = @PhaseListGuid
--

--
Insert Into dbo.cMat_InstallationPhase_C with (ROWLOCK)
Select RSRC, Deleted, Getdate(), DateChanged, @UserGUID, Null, Replace(CONVERT(varchar(255), NewID()),'-',''),
@InstallationPhasesGuidNew, ReadAccess, WriteAccess, InstallationPhase,SequenceNo,Code,Null,Null,Null
from dbo.cMat_InstallationPhase_C with (NOLOCK) where deleted = 0 and InstallationPhasesGUID = @PhaseListGuid

--
Insert Into dbo.cMat_InstallationSubPhase_C with (ROWLOCK)
Select RSRC, Deleted, getDate(), DateChanged, @UserGUID, Null, Replace(CONVERT(varchar(255), NewID()),'-',''),
ReadAccess, WriteAccess, InstallationSubPhase,InstallationPhaseGuid,SequenceNo
from dbo.cMat_InstallationSubPhase_C with (NOLOCK) where deleted = 0 and InstallationPhaseGUID in
(Select camosGUID from dbo.cMat_InstallationPhase_C where InstallationPhasesGUID = @InstallationPhasesGuidNew )

IF @@ERROR <> 0

BEGIN
-- Return 0 to the calling program to indicate failure.
ROLLBACK TRAN @TransactionName
Select 0 as ReturnState;
END
ELSE
BEGIN
-- Return 1 to the calling program to indicate success.
COMMIT TRAN @TransactionName
Select 1 as ReturnState;
END
END
----------------------------
i have an problem in insert data to "InstallationSubPhase_C" table
the above two tables inserted data is fine.

But "Subphase" table is an empty,

could anyone please let me know, Anybody please check my subquery for "Subphase" table.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/17/2013 :  22:51:31  Show Profile  Reply with Quote
how are you executing this procedure? what are parameter values passed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

teamjai
Starting Member

India
42 Posts

Posted - 01/18/2013 :  00:48:53  Show Profile  Reply with Quote
Exec c_Copy_InstallationPhases_C '|PhaseListGuid|',|UserGuid|
quote:
Originally posted by visakh16

how are you executing this procedure? what are parameter values passed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/18/2013 :  01:19:59  Show Profile  Reply with Quote
comment the inserts on table dbo.cMat_InstallationPhase_C inside procedure and see if you're getting any records returned by select statements

If they return the records, check the below statement

Select RSRC, Deleted, getDate(), DateChanged, @UserGUID, Null, Replace(CONVERT(varchar(255), NewID()),'-',''),
ReadAccess, WriteAccess, InstallationSubPhase,InstallationPhaseGuid,SequenceNo
from dbo.cMat_InstallationSubPhase_C with (NOLOCK) where deleted = 0 and InstallationPhaseGUID in
(Select camosGUID from dbo.cMat_InstallationPhase_C where InstallationPhasesGUID = @InstallationPhasesGuidNew )

and see if it returns related records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

teamjai
Starting Member

India
42 Posts

Posted - 01/19/2013 :  01:21:50  Show Profile  Reply with Quote
When I run this query, I get the following error:

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

But the point of the subquery was to in fact return more than one value.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/19/2013 :  02:14:37  Show Profile  Reply with Quote
which subquery? the above select wont throw this error as subquery is linked using IN operator. So I'm sure your statement is not exactly same as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.14 seconds. Powered By: Snitz Forums 2000