Solved

Create a sql function that Add two value from two joint table and place it in the master table

Posted on 2014-10-29
7
107 Views
Last Modified: 2014-11-06
I have the following table:   and I would like to write a function  that take the value  LocId Int Foreign key to LocTable and
DeptId Int Foreign key to DeptTable and add these two value such as A + ' ' + B and insert this value in a new column table.
([dbo].[AddTwoValuesPhone]([Location],[Department]))

Open in new window

USE [ztools]
GO
/****** Object:  UserDefinedFunction [dbo].[AddTwoValuesPhone]    Script Date: 10/29/2014 16:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<GUYON, Yves>
-- Create date: <200141027, ,>
-- Description:	<Phone Book, ,>
-- =============================================
ALTER FUNCTION [dbo].[AddTwoValuesPhone](@Location NVARCHAR(100),@Department NVARCHAR(150))

RETURNS NVARCHAR(150)
AS
 BEGIN
        DECLARE @Result NVARCHAR(150)
        SET @Result = @Location + ' - ' +  @Department   
        RETURN 
        (

            @Result
        )
    END

Open in new window



Client Table
ClientID Int
Name NVARCHAR(100)
LastName  NVARCHAR(100
LocId Int Foreign key to LocTable
DeptId Int Foreign key to DeptTable
LocId   This should be a value from the following table

DeptTable
Id
Name

LocTable
Id
Name
0
Comment
Question by:yguyon28
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40411860
What is a "new column table" and why is a view not sufficient?
0
 

Author Comment

by:yguyon28
ID: 40411865
Never done a view... No I just want the data to be placed back in a column in the client Table
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40412060
CREATE VIEW myFirstView 
AS 
    SELECT *, Location + ' ' + Department AS AddTwoValuesPhone 
    FROM [Client Table];

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:yguyon28
ID: 40413052
Thank you and how do I call this view to get populated from my table column?
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40413156
Use BOL: CREATE VIEW (Transact-SQL)

Creates a virtual table whose contents [..]

So it's a simple

SELECT *
FROM myFirstView ;

Open in new window

0
 

Author Comment

by:yguyon28
ID: 40426113
I've requested that this question be closed as follows:

Accepted answer: 0 points for yguyon28's comment #a40413052
Assisted answer: 250 points for ste5an's comment #a40412060
Assisted answer: 250 points for ste5an's comment #a40413156

for the following reason:

It work
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40421616
So you used a view or something else?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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