使用“内部联接"的SP用于插入查询； [英] SP for Insert Query using "inner Join"

问题描述

我在代码中编写了行查询，但是我试图转换为存储过程，只是卡在某些地方，请建议我改正错误.感谢Advance

即使我打算使用IF存在，也要使用update而不是select

这是我的内联代码查询

I written in line query in my code but I was trying to convert in to store procedure I just stuck some where,please advise me to correct the errors.Thanks in Advance

Even I am planning to use IF exist then use update rather than select

Here is my in line code query

INSERT INTO Phone (employeeno, PhoneNumber,CreatedBy,UpdatedBy)Select " + "employeeno" + "," + "phoneNo" + "," + "'" + "dbo" + "'" + "," + "'" + "dbo" + "'" + "from User where employeeno=" + item.EmployeeNumber;

ALTER PROC [dbo].[InsertPhone] 
                    @EmployeeNo int,
                    @PhoneNumber char(24)



DECLARE  @CONTEXT_INFO varchar(100)
					select   @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
 INSERT INTO [Phone]
							(employeeno
							,PhoneNumber
							,CreatedBy
							,UpdatedBy)
select  Ad.employeeno,Ad.PhoneNo,@CONTEXT_INFO,@CONTEXT_INFO from User AD where Ad.employeeno=@EmployeeNo

推荐答案

尝试类似的方法:

Try something like:

CREATE PROC [dbo].[InsertPhone]
      @EmployeeNo int
AS
BEGIN
      DECLARE @CONTEXT_INFO varchar(100)
      DECLARE @PhoneNo varchar(15)

      SELECT @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
      
      SELECT
             @PhoneNo = Ad.PhoneNo
      FROM
             User Ad
      WHERE Ad.Employeeno= @EmployeeNo


      INSERT INTO [Phone]
             (employeeno, PhoneNumber, CreatedBy, UpdatedBy)
      VALUES
             (@EmployeeNo, @PhoneNo, @CONTEXT_INFO, @CONTEXT_INFO) 
END

现在，如果需要对是否存在进行某些修改(这需要先通过if然后是UPDATE/INSERT语句)，然后进行相应的更改.

试试吧！

Now, if you need some modifications on exists or not (that would need a if and then UPDATE/INSERT staments), then make the changes accordingly.

Try!

这篇关于使用“内部联接"的SP用于插入查询；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！