| Author |
 Topic  |
|
|
teamjai
Starting Member
India
33 Posts |
 Posted - 01/17/2013 : 22:49:27
|
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
47189 Posts |
Posted - 01/17/2013 : 22:51:31
|
how are you executing this procedure? what are parameter values passed?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
teamjai
Starting Member
India
33 Posts |
Posted - 01/18/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 01/18/2013 : 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 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/
|
|
|
|
teamjai
Starting Member
India
33 Posts |
Posted - 01/19/2013 : 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
India
47189 Posts |
Posted - 01/19/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Problem in Stored procedure
