选择然后插入的存储过程 [英] stored procedure for select and then insert

问题描述

如何编写用于从tabel1获取参数值的存储过程
并在单个存储过程中将参数值插入到table2的参数值之后?

how to write stored procedure for get parameter values from tabel1
and after insert parameter values toameter values to table2 in single stored procedure?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--use webtemplate
ALTER procedure  [dbo].[ei_sp_LocationInsert] 
(
@Name varchar(100)=null,
@Code varchar(35)=null,
@Desc varchar(255)=null,
@CityID int=null,
@StateID int=null,
@CountryID int=null,
@Company int=null



)
as begin


select stateID,cityID,countryID from tabe1 where ID=@company
(
@stateID=stateID
@cityID=cityID
@countryID=countryID
)
insert into table2
(
[Name],
Code,
[Desc],
CityID,
StateID,
CountryID,
company

)
values
(
@Name,
@Code,
@Desc,
@CityID,
@StateID,
@CountryID,
@Company,

)




end

推荐答案

像这样更改并尝试

change like this and try

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--use webtemplate
ALTER procedure  [dbo].[ei_sp_LocationInsert] 
(
@Name varchar(100)=null,
@Code varchar(35)=null,
@Desc varchar(255)=null,
@CityID int=null,
@StateID int=null,
@CountryID int=null,
@Company int=null
 

 
)
as 
begin
 


select  @stateID=stateID from tabe1 where ID=@company
select   @cityID=cityID  from tabe1 where ID=@company
select  @countryID=countryID from tabe1 where ID=@company
 
insert into table2
(
[Name],
Code,
[Desc],
CityID,
StateID,
CountryID,
company
 
)
values
(
@Name,
@Code,
@Desc,
@CityID,
@StateID,
@CountryID,
@Company,
 
)
 

 

end

设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
GO
--use webtemplate
ALTER过程[dbo].[ei_sp_LocationInsert]
(
@Name varchar(100)=空，
@Code varchar(35)= null，
@Desc varchar(255)= null，
@CityID int = null，
@StateID int = null，
@CountryID int = null，
@Company int = null
)
作为
开始
从tabe1中选择@ stateID = stateID，@ cityID = cityID，@ countryID = countryID，其中ID = @ company

插入table2
(
[名称]，
代码，
[Desc]，
CityID，
StateID，
CountryID，
公司

)
值
(
@Name，
@Code，
@Desc，
@CityID，
@StateID，
@CountryID，
@Company，

)
结束

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--use webtemplate
ALTER procedure [dbo].[ei_sp_LocationInsert]
(
@Name varchar(100)=null,
@Code varchar(35)=null,
@Desc varchar(255)=null,
@CityID int=null,
@StateID int=null,
@CountryID int=null,
@Company int=null
)
as
begin
select @stateID=stateID ,@cityID=cityID, @countryID=countryID from tabe1 where ID=@company

insert into table2
(
[Name],
Code,
[Desc],
CityID,
StateID,
CountryID,
company

)
values
(
@Name,
@Code,
@Desc,
@CityID,
@StateID,
@CountryID,
@Company,

)
end

这篇关于选择然后插入的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！