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
103 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 32

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 32

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

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

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 32

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now