3? Assignment 5 Create Function(2) -Saved to my Mac Design LayoutReferences Mail
ID: 3902756 • Letter: 3
Question
3? Assignment 5 Create Function(2) -Saved to my Mac Design LayoutReferences Mailings Review View No Soacing Heading ASSIGNMENT 5 -CREATING FUNCTIONS Assignment Points: 40 Create 1 function that will print out an address label for the TAL Distributors database. You can specify the customer table to output the address. an example for customer is: Toys Galore 28 Laketon St. Fullton, CA 90085 RESULTS 1. Create the function to output the address. 2. Run your function against the Test database to ensure there are no errors. 3. Include the SQL statement to create the function as well as a statement to run it in I file.Explanation / Answer
According to the given query, Here i create a function for print the address label of customer for the TAL Distributors database.
First Create a table for customer:
CREATE TABLE [dbo].[CUSTOMER](
[CUSTOMER_NUM] [int] NOT NULL,
[CUSTOMER_NAME] [varchar](250) NULL,
[STREET] [varchar](250) NULL,
[CITY] [varchar](250) NULL,
[STATE] [varchar](250) NULL,
[POSTAL_CODE] [int] NULL,
[BALANCE] [varchar](50) NULL,
[CREDIT_LIMIT] [varchar](50) NULL,
[REP_NUM] [int] NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[CUSTOMER_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here CUSTOMER_NUM is an unique key. Using this table we print the address of customer using Scalar-valued Function in MS SQL server.
Insert row in this table using insert statement :
INSERT INTO [dbo].[CUSTOMER] VALUES (126,'TOYS GALORE','28 Laketon St.','Fullton','CA',90085,'$1,210.25','$7,500.00',15)
pass a parameter @CUSTOMER_NUM in the function "CUSTOMER_ADDRESS". Because CUSTOMER_NUM is an unique key and we can get all data of customer using this key.
Following function return the address of customer:
CREATE FUNCTION [dbo].[CUSTOMER_ADDRESS]
(
-- Add the parameters for the function here
@CUSTOMER_NUM INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
-- Declare the return variable here
DECLARE @ADDRESS VARCHAR(MAX)
-- Declare for new line creation
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
-- Check the customer id is exist or not
IF EXISTS(SELECT * FROM [DB_DEMO].dbo.CUSTOMER WHERE CUSTOMER_NUM=@CUSTOMER_NUM)
BEGIN
-- Assign result to return variable
SET @ADDRESS= (SELECT A.CUSTOMER_NAME FROM [DB_DEMO].dbo.CUSTOMER AS A WHERE A.CUSTOMER_NUM=@CUSTOMER_NUM)+@NewLineChar+(SELECT A.STREET FROM [DB_DEMO].dbo.CUSTOMER AS A WHERE A.CUSTOMER_NUM=@CUSTOMER_NUM)+@NewLineChar+(SELECT A.CITY+','+A.STATE+' '+CONVERT(VARCHAR(50),A.POSTAL_CODE) FROM [DB_DEMO].dbo.CUSTOMER AS A WHERE A.CUSTOMER_NUM=@CUSTOMER_NUM)
-- Return the result of the function
RETURN @ADDRESS
END
ELSE
BEGIN
SET @ADDRESS='CUSTOMER_NUM IS NOT EXIST IN DATABASE....'
-- Return the result of the function
RETURN @ADDRESS
END
SET @ADDRESS='PLEASE ENTER VALID CUSTOMER_NUM....'
-- Return the result of the function
RETURN @ADDRESS
END
Run this function :
PRINT [DB_DEMO].[dbo].[CUSTOMER_ADDRESS](126)
After execution it will show this following result :
TOYS GALORE
28 Laketon St.
Fullton,CA 90085
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.