SQL - Stored Procedure help

[SoftwareNinja]

Hi all,

 

I have just started working with Stored Procedures and I have been given this task blow.

Would anyone be able to help me under stand all the areas highlighted below.

 

Thank you in advance!

 

------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Review the stored procedure “spProfileInformation__c” below. Provide feedback in the
following areas:

• Syntax Errors
• Common Error
• Potential performance improvement
• Any general bugs

 

The Stored Procedure:

USE [APTIFY]
GO
/****** Object: StoredProcedure [dbo].[ spProfileInformation__C] Script Date: 21/04/2021
15:48:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[ spProfileInformation__C](
@PersonID int)
as
BEGIN
SELECT p.FirstName,
p.MiddleName ,
x.LastName,
p.Prefix,
Replace(p.Suffix,' ','') as Suffix,
w.UserID,
p.PracticingStatusID_Name,
p.LawDirectoryOfficPhone__c,
p.IncludeLawDirectoryOfficePhone__c,
p.LawDirectoryMobile__c,
p.IncludeLawDirectoryMobile__c,
c.MainEmail FirmEmailAddress,
dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line1') FirmAddress1,
dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line2') FirmAddress2,
dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line3') FirmAddress3,
dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line4') FirmAddress4,
dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line5') FirmAddress5,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'postcode') FirmPostCode,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'dx') FirmDX,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'county') FirmCounty,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'country') FirmCountry,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'countrycode') FirmCountryCode,
dbo.fnGetFirmAddressLinebyCompany__c(c.id,'countrySort') FirmCountrySort,
p.PracticingStatus__c,
p.PracticingSubStatus__c,
from vwPersons p
INNER JOIN WebUser w on w.LinkID=p.id
INNER JOIN MemberType m on m.id=p.MemberTypeID
LEFT OUTER JOIN vwCompanies c on c.id=p.CompanyID
WHERE P.ID=@ID
END

 

[Franck]

You wont learn if other people do your homework. You should try to understand and ask which part you cant figure out. Much easier to learn when you get explanation customized to your misunderstanding.

[SoftwareNinja]

3 hours ago, Franck said:

You wont learn if other people do your homework. You should try to understand and ask which part you cant figure out. Much easier to learn when you get explanation customized to your misunderstanding.

Completely agree with you but stored procedures is something I have only started looking at. Would you be able to point me in a direction to fully understand this?

 

 

[Unhelpful]

11 minutes ago, ScottDurkin said:

Completely agree with you but stored procedures is something I have only started looking at. Would you be able to point me in a direction to fully understand this?

 

 

Do you have a specific thing you're confused about or are you having issues knowing where to start?

[SoftwareNinja]

57 minutes ago, JoeyDM said:

Do you have a specific thing you're confused about or are you having issues knowing where to start?

So these are some of the issues I think are present in the SP.]

• The H.id should be c.id
• When creating the nicknames I feel it should be "from vwPersons AS p" appose to "from vwPersons p". Same applies to the other nicknames just to make it stand out more.
• Some of the Syntax words are lower case rather than upper case i.e. from, on, as, procedure etc, etc.

As for performance increasing and or errors, i'm not entirely sure.

[SoftwareNinja]

5 hours ago, JoeyDM said:

Do you have a specific thing you're confused about or are you having issues knowing where to start?

I'm also not sure of the "' '" within the procedure.

[Franck]

14 hours ago, ScottDurkin said:

I'm also not sure of the "' '" within the procedure.

the ' is a bad copy paste you must have made. In octal that represent the apostrophe '

 

so the line

dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line1') FirmAddress1,

is actually

dbo.fnGetFirmAddressLinebyCompany__c(H.id,'line1') FirmAddress1,

 

It must have been a mistake on the copy paste because I do not believe it's on the level of knowledge a beginner is expected to have.