Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-17 : 22:49:27
|
I am trying to insert data from table .I have the stored procedure definition as followsALTER PROCEDURE [dbo].[c_Copy_InstallationPhases_C] -- Add the parameters for the stored procedure here @PhaseListGuid varchar(36) = NULL, @UserGUID int = NULLASSET XACT_ABORT ONBEGIN -- 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 TransactionBEGIN 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; ENDELSE BEGIN -- Return 1 to the calling program to indicate success. COMMIT TRAN @TransactionName Select 1 as ReturnState; ENDEND----------------------------i have an problem in insert data to "InstallationSubPhase_C" tablethe 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
52326 Posts |
Posted - 2013-01-17 : 22:51:31
|
how are you executing this procedure? what are parameter values passed?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-18 : 00:48:53
|
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 MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 01:19:59
|
comment the inserts on table dbo.cMat_InstallationPhase_C inside procedure and see if you're getting any records returned by select statementsIf they return the records, check the below statementSelect RSRC, Deleted, getDate(), DateChanged, @UserGUID, Null, Replace(CONVERT(varchar(255), NewID()),'-',''),ReadAccess, WriteAccess, InstallationSubPhase,InstallationPhaseGuid,SequenceNofrom 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 MVPhttp://visakhm.blogspot.com/ |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-19 : 01:21:50
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-19 : 02:14:37
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|