Problem in Stored procedure - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem in Stored procedure

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 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

52326 Posts

Posted - 2013-01-17 : 22:51:31

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://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 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
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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources