在Storeprocedure中使用联接插入的问题 [英] Problem with Insert using joins in Storeprocedure

问题描述

你好，
我有一个使用左联接的插入语句，查询工作正常，但是当我在storeprocedure中使用它时，存储过程未在表中插入任何数据.
请帮忙.

Hello,
I have a Insert statment using left joins the query works fine, but when i use it in storeprocedure , then the store procedure is not inserting any data into the table.
please help.

推荐答案

请尝试这个

Hi,

Please try this

Create Procedure test_Sp 
As 
Begin 
     insert into Test 
     select a.Name,b.sub from 
     IdTable b join Nametable a ON b.no = a.no 
     where b.no like ''x_%'' end

我也为此创建了一个示例，

Also i have create one sample for it like,

Create table #IdTable([No] varchar(10),Name varchar(20))
Insert into #IdTable values(''11111'',''AA'')
Insert into #IdTable values(''22222'',''BB'')
Insert into #IdTable values(''33333'',''CC'')
Insert into #IdTable values(''44444'',''DD'')
Insert into #IdTable values(''55555'',''EE'')
 

Create table #Nametable(ID bigint, [No] varchar(10),sub varchar(20))
Insert into #Nametable values(1,''11111'',''A1'')
Insert into #Nametable values(2,''22222'',''B1'')
Insert into #Nametable values(3,''11111'',''A2'')
Insert into #Nametable values(4,''22222'',''B2'')
Insert into #Nametable values(5,''33333'',''C1'')

Create Table #Test (Name varchar(20),Sub varchar(20))

insert into #Test

select a.Name,
b.sub 
from #Nametable b join #IdTable a ON b.no = a.no where b.no like ''1%'' 


select * from #Test 
Drop table #IdTable
Drop table #Nametable
Drop table #Test 

这篇关于在Storeprocedure中使用联接插入的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！