Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
I'm facing an error 'Must declare the scalar variable "@MyTableVariable" ' while executing my procedure in SQL server 2005.
Below is my code.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 @mappingdetails TABLE (
 mapping_id varchar(40),
 folder_name varchar(40),
 mapping_name varchar(40),
 session_id varchar(40))
 
INSERT into @mappingdetails(
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);
 
IF (select distinct mapping_id from TEMP_IDEA.dbo.MAPPING)=@mappingdetails .mapping_id
            and
           (select distinct folder_name from TEMP_IDEA.dbo.MAPPING)=@mappingdetails .folder_name
  Begin
   	        SET @start_tran =1
	        BEGIN TRANSACTION
update TEMP_IDEA.dbo.MAPPING
set mapping_id=@mappingdetails .mapping_id
end
  Else
begin
  insert into TEMP_IDEA.dbo.MAPPING(mapping_id,folder_name) values(@mappingdetails .mapping_id,@mappingdetails .folder_name)
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
Posted 15-Oct-12 0:41am
Edited 15-Oct-12 0:42am
_Amy50.8K
v2
Comments
_Amy at 15-Oct-12 6:45am
   
I am unable to find the @MyTableVariable in your SP. Check it again and let me know for the issue.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

@mappingdetails defined table variable .
 
I'm facing an error 'Must declare the scalar variable @mappingdetails" ' while executing my procedure in SQL server 2005.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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))
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
Change below code
 
Select @mappingdetails.mapping_id from @mappingdetails
 
Like this
 
Select Tmp.mapping_id from @mappingdetails as Tmp
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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 =, !=, <, <= , >, >
:
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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 15 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100