[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

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

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
yguyon28
Asked:
yguyon28
  • 4
  • 3
1 Solution
 
ste5anSenior DeveloperCommented:
What is a "new column table" and why is a view not sufficient?
0
 
yguyon28Author Commented:
Never done a view... No I just want the data to be placed back in a column in the client Table
0
 
ste5anSenior DeveloperCommented:
CREATE VIEW myFirstView 
AS 
    SELECT *, Location + ' ' + Department AS AddTwoValuesPhone 
    FROM [Client Table];

Open in new window

0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
yguyon28Author Commented:
Thank you and how do I call this view to get populated from my table column?
0
 
ste5anSenior DeveloperCommented:
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
 
yguyon28Author Commented:
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
 
ste5anSenior DeveloperCommented:
So you used a view or something else?
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now