在sql server中执行过程时收到错误 [英] getting an error while executing the procedure in sql server

问题描述

在SQL Server 2005中执行我的过程时，遇到错误必须声明标量变量"@MyTableVariable".
以下是我的代码.请帮助我.

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

推荐答案

@mapping详细定义表变量.

在SQL Server 2005中执行我的过程时，我遇到错误必须声明标量变量@mappingdetails".

@mappingdetails defined table variable .

I''m facing an error ''Must declare the scalar variable @mappingdetails" '' while executing my procedure in SQL server 2005.

而不是像这样创建临时表

声明@mappingdetails TABLE(
mapping_id varchar(40)，
folder_name varchar(40)，
mapping_name varchar(40)，
session_id varchar(40))

尝试

创建表#mappingdetails(mapping_id varchar(40)，
folder_name varchar(40)，
mapping_name varchar(40)，
session_id varchar(40))

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

这篇关于在sql server中执行过程时收到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！