Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inner Join for Insert and Update query in Store Proc

Posted on 2011-03-22
11
Medium Priority
?
424 Views
Last Modified: 2012-05-11
i written in line query in my code but i was trying to convert in to store proc i just stuck some where,please advise me to correct the errors.Thanks in Advance

Even am planning to use IF exist then use update rather than insert

Here is my in line code query and it works good but am trying to write sp

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

Open in new window


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 ADUser AD where Ad.employeeno=@EmployeeNo

Open in new window

0
Comment
Question by:Sha1395
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Assisted Solution

by:mkobrin
mkobrin earned 200 total points
ID: 35187574
when you create a stored proc you must use the AS clause
CREATE PROC [dbo].[InsertPhone] 
                    @EmployeeNo int,
                    @PhoneNumber char(24)

AS

DECLARE  @CONTEXT_INFO varchar(100)
select   @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
IF NOT EXISTS (select employeeno from Phone where employeeno = @EmployeeNo and PhoneNumber = @PhoneNumber)
BEGIN
INSERT INTO [Phone]
							(employeeno
							,PhoneNumber
							,CreatedBy
							,UpdatedBy)
select  Ad.employeeno,Ad.PhoneNo,@CONTEXT_INFO,@CONTEXT_INFO from ADUser AD where Ad.employeeno=@EmployeeNo
END
--here you can put in an else clause to update the record if required.

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35187576
hi,

  please clarify if you are working with MySQL or MS SQL Server (and which version)
0
 
LVL 4

Assisted Solution

by:qasim_md
qasim_md earned 400 total points
ID: 35187615
try this... let me know if it helped...

ALTER PROC [dbo].[InsertPhone]
                    @EmployeeNo int,
                    @PhoneNumber char(24)
as
begin
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 ADUser AD where Ad.employeeno=@EmployeeNo
end
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Sha1395
ID: 35187768
Thanks for all the comments.

Am working on MS Sql server 2005
0
 

Author Comment

by:Sha1395
ID: 35187814
so the problem for my code is missed "AS" Clause right ?

Here is my whole sp inclueded Update condition,please correct me if am wrong

ALTER PROC [dbo].[InsertPhone]
                    @EmployeeNo int,
                    @PhoneNumber char(24)
as
begin
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 ADUser AD where Ad.employeeno=@EmployeeNo
end
Else
Begin
select   @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
UPDATE o SET o.employeeno = c. employeeno ,o.PhoneNumber =c.PhoneNumber,o.CreatedBy=@CONTEXT_INFO,O.UpdatedBy=@CONTEXT_INFOT FROM Phone o INNER JOIN ADUser c ON  c.EmployeeNo = o.EmployeeNo WHERE c.EmployeeNo = " + item.EmployeeNumber;
END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1400 total points
ID: 35187885
I understand you want  to update "if exists", otherwise insert
ALTER PROC [dbo].[InsertPhone]
( @EmployeeNo int
, @PhoneNumber char(24)
)
as
begin
declare @CONTEXT_INFO varchar(100)
select @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

UPDATE o 
  SET o.employeeno = c.employeeno 
    , o.PhoneNumber =c.PhoneNumber
    , o.CreatedBy=@CONTEXT_INFO
    , O.UpdatedBy=@CONTEXT_INFOT 
 FROM Phone o 
 JOIN ADUser c 
   ON c.EmployeeNo = o.EmployeeNo 
WHERE c.EmployeeNo =  @EmployeeNo

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO [Phone]
    ( employeeno
    , PhoneNumber
    , CreatedBy
    , UpdatedBy
    )
   SELECT Ad.employeeno
        , Ad.PhoneNo
        , @CONTEXT_INFO
        , @CONTEXT_INFO 
     FROM ADUser AD 
    WHERE Ad.employeeno=@EmployeeNo

END

Open in new window

0
 
LVL 7

Expert Comment

by:mkobrin
ID: 35187913
I think this is what you are looking for:

I have made @CONTEXT_INFO  bigger because you have used 128 characters in your convert in the select statement
0
 

Author Comment

by:Sha1395
ID: 35187955
Thanks for all your help,i think Angel III gave code (awesome).
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 35187957
Note that if the SP exists then you can alter it. If it does not exists you have to create it.

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

AS

DECLARE  @CONTEXT_INFO varchar(100)
select   @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
IF NOT EXISTS (select employeeno from Phone where employeeno = @EmployeeNo and PhoneNumber = @PhoneNumber)
BEGIN
INSERT INTO [Phone]
							(employeeno
							,PhoneNumber
							,CreatedBy
							,UpdatedBy)
select  Ad.employeeno,Ad.PhoneNo,@CONTEXT_INFO,@CONTEXT_INFO from ADUser AD where Ad.employeeno=@EmployeeNo
END
ELSE
BEGIN
update [Phone] set PhoneNumber= @PhoneNumber, UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo 
END

Open in new window

0
 

Author Comment

by:Sha1395
ID: 35188065
thanks mkobrin,already i wrote SP for simple insert and update now i will atlter
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question