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
109 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
[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
7 Comments
 
LVL 34

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 34

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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

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

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 34

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Viewers will learn how the fundamental information of how to create a table.
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…

729 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