getting an error while executing the procedure in sql server

instead of creating temp table like this

DECLARE @mappingdetails TABLE (
mapping_id varchar(40),
folder_name varchar(40),
mapping_name varchar(40),
session_id varchar(40))

try

create table #mappingdetails ( mapping_id varchar(40),
folder_name varchar(40),
mapping_name varchar(40),
session_id varchar(40))

Hi,

Change below code

Select @mappingdetails.mapping_id from @mappingdetails

Like this

Select Tmp.mapping_id from @mappingdetails as Tmp

thanks all for your reply.
have chnaged table into cursor.getting an error "

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

:
please help me.

USE [TEMP_IDEA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter proc [dbo].[LOAD_MAPPING_TABLE]@LoadFlag int OUTPUT
As
Begin
DECLARE @ERR int
DECLARE @start_tran int
SET @LoadFlag=1
SET @start_tran =0
DECLARE @mapping_id varchar(40)
DECLARE @folder_name varchar(40)
DECLARE @mapping_name varchar(40)
DECLARE @session_id varchar(40)
DECLARE CUR1 CURSOR FOR
select distinct mapping_id, mapping_name, session_id, subject_area
from
(
select rls.subject_area,rls.session_name, rls.session_id, rls.mapping_name, rsm.mapping_id,
rls.subject_id,
rsm.source_id, rsm.source_name, a.target_id, a.target_name
from
dbo.FD_INFA_REP_LOAD_SESSIONS rls,
dbo.FD_INFA_REP_SRC_MAPPING rsm,
(
SELECT
OPB_TARG.TARGET_NAME TARGET_NAME,
OPB_TARG.TARGET_ID TARGET_ID,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_MAPPING.MAPPING_NAME MAPPING_NAME,
OPB_WIDGET_INST.COMMENTS DESCRIPTION,
OPB_MAPPING.COMMENTS MAPPING_COMMENT
FROM
dbo.FD_INFA_OPB_TARG OPB_TARG,
dbo.FD_INFA_OPB_SUBJECT OPB_SUBJECT,
dbo.FD_INFA_OPB_MAPPING OPB_MAPPING,
dbo.FD_INFA_OPB_TDS OPB_TDS,
dbo.FD_INFA_OPB_WIDGET_INST OPB_WIDGET_INST,
dbo.CONTROL_TABLE CNTL
WHERE
OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_WIDGET_INST.WIDGET_ID = OPB_TARG.TARGET_ID
AND OPB_TDS.TARGET_INSTANCE_ID = OPB_WIDGET_INST.INSTANCE_ID
AND OPB_TARG.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.VERSION_NUMBER = OPB_WIDGET_INST.VERSION_NUMBER
AND OPB_MAPPING.VERSION_NUMBER = OPB_TDS.VERSION_NUMBER
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_TARG.IS_VISIBLE = 1
AND(datediff (ss,CNTL.FN_Last_UpdateTime,OPB_MAPPING.LAST_SAVED)>0
OR datediff (ss,CNTL.FN_Last_UpdateTime,OPB_TARG.LAST_SAVED)>0
)
) A
where
rls.mapping_name = rsm.mapping_name
and
rls.subject_area = rsm.subject_area
and
rls.IS_VALID = 1
and A.mapping_name = rls.mapping_name
and A.subject_area = rls.subject_area)b;

OPEN CUR1
FETCH next from CUR1 INTO @mapping_id,@mapping_name,@session_id ,@folder_name
--WHILE @@FETCH_STATUS = 0
IF (select distinct mapping_id from TEMP_IDEA.dbo.MAPPING)=@mapping_id
and
(select distinct folder_name from TEMP_IDEA.dbo.MAPPING)=@folder_name
begin
SET @start_tran =1
BEGIN TRANSACTION
update TEMP_IDEA.dbo.MAPPING
set mapping_id=@mapping_id,
mapping_name=@mapping_name,
session_id=@session_id,
folder_name=@folder_name
SET @ERR =@@Error
if @ERR=0 SET @LoadFlag=0
end
Else

insert into TEMP_IDEA.dbo.MAPPING(mapping_id, mapping_name, session_id,folder_name)
values(@mapping_id,@mapping_name,@session_id,@folder_name)
SET @ERR =@@Error
if @ERR=0 SET @LoadFlag=0


close cur1
--BEGIN
-- print 'load sucessful'
-- SET @LoadFlag=0
--
--
-- ELSE
-- BEGIN
-- print 'Data load falied '
-- END
--
-- print @LoadFlag
--
--
-- IF @start_tran =1
-- BEGIN
-- IF @LoadFlag =0
-- BEGIN
-- COMMIT TRANSACTION
-- print' load successful'
-- END
-- ELSE
-- BEGIN
-- ROLLBACK TRANSACTION
-- print 'Load failed'
-- END
-- END
end

Hi Srikant,
I tried using temp table but getting an error in 'update TEMP_IDEA.dbo.MAPPING
set mapping_id=@mapping_id.Please advise.

Msg 102, Level 15, State 1, Procedure test, Line 77
Incorrect syntax near '='.



USE [TEMP_IDEA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[test]@LoadFlag int OUTPUT
As
Begin
DECLARE @ERR int
DECLARE @start_tran int
DECLARE @MAPPING_ID VARCHAR(40)
DECLARE @FOLDER_NAME VARCHAR(40)
SET @LoadFlag=1
SET @start_tran =0
create table #mappingdetails(mapping_id varchar(40),mapping_name varchar(40),session_id varchar(40),folder_name varchar(40));

INSERT into #mappingdetails(mapping_id, mapping_name, session_id, folder_name)
(select distinct mapping_id, mapping_name, session_id, subject_area
from
(
select rls.subject_area,rls.session_name, rls.session_id, rls.mapping_name, rsm.mapping_id,
rls.subject_id,
rsm.source_id, rsm.source_name, a.target_id, a.target_name
from
dbo.FD_INFA_REP_LOAD_SESSIONS rls,
dbo.FD_INFA_REP_SRC_MAPPING rsm,
(
SELECT
OPB_TARG.TARGET_NAME TARGET_NAME,
OPB_TARG.TARGET_ID TARGET_ID,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_MAPPING.MAPPING_NAME MAPPING_NAME,
OPB_WIDGET_INST.COMMENTS DESCRIPTION,
OPB_MAPPING.COMMENTS MAPPING_COMMENT
FROM
dbo.FD_INFA_OPB_TARG OPB_TARG,
dbo.FD_INFA_OPB_SUBJECT OPB_SUBJECT,
dbo.FD_INFA_OPB_MAPPING OPB_MAPPING,
dbo.FD_INFA_OPB_TDS OPB_TDS,
dbo.FD_INFA_OPB_WIDGET_INST OPB_WIDGET_INST,
dbo.CONTROL_TABLE CNTL
WHERE
OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_WIDGET_INST.WIDGET_ID = OPB_TARG.TARGET_ID
AND OPB_TDS.TARGET_INSTANCE_ID = OPB_WIDGET_INST.INSTANCE_ID
AND OPB_TARG.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.VERSION_NUMBER = OPB_WIDGET_INST.VERSION_NUMBER
AND OPB_MAPPING.VERSION_NUMBER = OPB_TDS.VERSION_NUMBER
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_TARG.IS_VISIBLE = 1
AND(datediff (ss,CNTL.FN_Last_UpdateTime,OPB_MAPPING.LAST_SAVED)>0
OR datediff (ss,CNTL.FN_Last_UpdateTime,OPB_TARG.LAST_SAVED)>0
)
) A
where
rls.mapping_name = rsm.mapping_name
and
rls.subject_area = rsm.subject_area
and
rls.IS_VALID = 1
and A.mapping_name = rls.mapping_name
and A.subject_area = rls.subject_area)B);
SET @MAPPING_ID=(select distinct mapping_id from #mappingdetails WHERE mapping_id IS NOT NULL);
SET @folder_name=(SELECT DISTINCT FOLDER_NAME FROM #mappingdetails WHERE FOLDER_NAME IS NOT NULL);

IF (select distinct mapping_id from TEMP_IDEA.dbo.MAPPING)=@mapping_id
and
(select distinct folder_name from TEMP_IDEA.dbo.MAPPING)=@folder_name
Begin
SET @start_tran =1
BEGIN TRANSACTION
update TEMP_IDEA.dbo.MAPPING
set mapping_id=@mapping_id
end
Else
begin
insert into TEMP_IDEA.dbo.MAPPING(mapping_id,folder_name) values(@mapping_id,@folder_name)

DROP TABLE =#mappingdetails;
end
print @LoadFlag
IF @start_tran =1
BEGIN
IF @LoadFlag =0
BEGIN
COMMIT TRANSACTION
print' load successful'
END
ELSE
BEGIN
ROLLBACK TRANSACTION
print 'Load failed'
end
end
END