Stored Procedure
Can someone check to see if I creating this stored procedure correctly.
What I'm doing is passing customer zip to the procedure then looking up to see who is the sales rep in that area. I want to be able to return the reps name, address, city, state, phone and email
-- Drop Procedure if already exists
IF OBJECT_ID('fn_salesrep_lookup_custzip') IS NOT NULL
DROP PROC fn_salesrep_lookup_custzip
GO
-- Create Procedure using temp variables as input variables
CREATE Procedure fn_salesrep_lookup_custzip
(
@CustomerZip Varchar(6)
)
AS
BEGIN
DECLARE
@Name nvarchar(50)
,@Address nvarchar(50)
,@City nvarchar(50)
,@State nvarchar(50)
,@Zip nvarchar(50)
,@Phone nvarchar(50)
,@email nvarchar(50);
-- Get contact information
SELECT
@Name =LTRIM(RTRIM(T1.[SM-NAME]))
,@Address =LTRIM(RTRIM(T1.[SM-ADDRESS1]))+
CASE
WHEN ISNULL(T1.[SM-ADDRESS2],'')='' THEN ''
ELSE ', '+LTRIM(RTRIM(T1.[SM-ADDRESS2]))
END
,@City =LTRIM(RTRIM(T1.[SM-CITY]))
,@State =LTRIM(RTRIM(T1.[SM-STATE]))
,@Zip =CASE
WHEN LEN(LTRIM(RTRIM(T1.[SM-ZIP])))=6 AND LEFT(LTRIM(T1.[SM-ZIP]),1)='0' THEN RIGHT(RTRIM(T1.[SM-ZIP]),5)
ELSE LTRIM(RTRIM(T1.[SM-ZIP]))
END
,@Phone =LTRIM(RTRIM(T1.[SM-PHONE]))
,@email ='email not yet available'
FROM dbo.SalesRep T1
INNER JOIN dbo.SalesRepCustomerZip T2 ON T1.[SM-NBR]=T2.[SM-NBR]
WHERE
CASE
WHEN LEN(LTRIM(RTRIM(T2.ZIP)))=6 AND LEN(LTRIM(RTRIM(@CustomerZip)))=6 THEN LTRIM(RTRIM(T2.ZIP))
WHEN LEN(LTRIM(RTRIM(T2.ZIP)))=6 AND LEFT(LTRIM(T2.ZIP),1)='0' THEN RIGHT(RTRIM(T2.ZIP),5)
ELSE LTRIM(RTRIM(T2.ZIP))
END
=@CustomerZip;
-- Return the information to the user
IF @CustomerZip IS NOT NULL AND @Name IS NOT NULL
SELECT @Name, @Address, @City, @State, @Zip, @Phone, @email
End
GO
|
