Slightly Complicated SQL Stored Procedure

Morning Experts,

I would be really grateful if you could review my code below, it is a stored procedure that firstly validates that the information being supplied is correct, and then inserts the data before returning a result...

What i need to do is only return 1 result, which I can then process on the ASP page calling the SP

I suspect that this will not achieve the desired result at present though ...

CREATE PROCEDURE [dbo].[JBBeanInsert2]
@username nvarchar(255),
@password nvarchar(50),
@name nvarchar(50),
@phone nvarchar(30),
@jobtitle nvarchar(64),
@description nvarchar(max),
@payrate nvarchar(250),
@category nvarchar(50),
@employmenttype nvarchar(50),
@reference nvarchar(250),
@locationid int,
@startdate nvarchar(50),
@altemail nvarchar(255),
@full_part nvarchar(9)
AS
/*Declare additional variables */
DECLARE @SiteID int
DECLARE @SITEURL NVARCHAR(225)
DECLARE @SITEURLSHORT NVARCHAR(225)
DECLARE @SITENOREPLY NVARCHAR(225)
DECLARE @SITEEMAIL NVARCHAR(225)
DECLARE @LOCATION NVARCHAR(50)
DECLARE @EMPLOYEEID int
DECLARE @CLIENTID int
DECLARE @PASSWORDCorrect char(1)
DECLARE @ACCOUNTLIVE char(1)
DECLARE @ACCOUNTTYPE char(1)
DECLARE @Hours int
DECLARE @INSERTEDID INT
DECLARE @PostResult int 
DECLARE @CLIENTURL NVARCHAR(225)
DECLARE @ADMINURL NVARCHAR(225)
DECLARE @CLIENTEMAIL CHAR(1)

BEGIN
SET NOCOUNT ON
IF @full_part = 'full_part' SET @Hours = 1
IF @full_part = 'Part-Time' SET @Hours = 2

/*Check Location Exists and if so set Site Variables */
IF NOT EXISTS (SELECT JBLSiteID FROM dbo.JBLocation WHERE JBLID = @LocationID)
BEGIN
SELECT 
@PostResult = 0
END
ELSE
BEGIN
Select 
@SiteID = S.JBSSiteID, 
@SITEURL = S.JBSURL, 
@SITEURLSHORT = S.JBSURLShort, 
@SITENOREPLY = S.JBSNoReplyEmail, 
@SITEEMAIL = S.JBSEmail, 
@LOCATION = L.JBLocation
From dbo.JBSite S inner join dbo.JBLocation L on L.JBLSiteID = S.JBSSiteID
Where L.JBLID = @LocationID
END
/*Check that Category exists for specified site*/
	IF NOT EXISTS(Select JBCategoryLabel from dbo.JBCategories Where JBCSiteID = @SiteID AND JBCategoryLabel = @category)
	BEGIN
	SELECT 
	@PostResult = 19
	END
/* IF SITEID IS NOT NULL CHECK LOCATION AND EMPLOYEE EXIST */
	IF @SiteID <> NULL
	BEGIN
/*Check that a Recruiter Account exists with the specified Username, if so set Employee And Client ID values */ 
	IF NOT EXISTS (SELECT ID FROM dbo.Employee WHERE Username = @username)
	BEGIN
	SELECT
	@PostResult = 15,
	@CLIENTURL = @SITEURL +'/coms/mp/mpfailure.asp?ID=1&SID=' + @SiteID + '&jobtitle=',
	@ADMINURL = @SITEURL +'/coms/mp/wmmpfailure.asp?ID=1&SID =' + @SiteID + '&jobtitle='
	END
	ELSE
	BEGIN
	Select 
	@EMPLOYEEID = ID, 
	@CLIENTID = ClientID
	From dbo.Employee
	Where Username = @username
	END
/*LOCATION AND EMPLOYEE CHECKED */	
	END
/*Check that the Recruiter Password is correct if so set PasswordCorrect = 'Y' */
IF @EMPLOYEEID <> NULL
BEGIN
IF NOT EXISTS (Select ID From dbo.Employee Where Username = @username AND [Password] = @password)
BEGIN
SELECT
@PostResult = 17,
@CLIENTURL = @SITEURL +'/coms/mp/mpfailure.asp?ID=3&SID=' + @SiteID + '&UID=' + @EMPLOYEEID + '&jobtitle=',
@ADMINURL = null
END
ELSE
BEGIN
SET @PASSWORDCorrect = 'Y'
END
END

/* If the Password is correct check that the client account is live or has tokens IF not redirect*/ 
IF @PASSWORDCorrect = 'Y'
BEGIN
IF NOT EXISTS(SELECT E.ID FROM dbo.Employee E INNER JOIN dbo.Client C ON E.ClientID = C.ID WHERE E.ID = @EMPLOYEEID AND C.AccountType = 'a' AND C.Tokens > 0 OR E.ID = @EMPLOYEEID AND C.AccountType = 'm' AND C.StartDate < GETDATE() AND C.EndDate > GETDATE())
BEGIN
Select
@PostResult = 18,
@CLIENTURL = @SITEURL +'/coms/mp/mpfailure.asp?ID=4&SID=' + @SiteID + '&UID=' + @EMPLOYEEID + '&jobtitle=',
@ADMINURL = @SITEURL +'/coms/mp/wmmpfailure.asp?ID=4&SID =' + @SiteID + '&jobtitle='
END
ELSE
BEGIN
SET @ACCOUNTLIVE = 'Y'
SELECT @CLIENTEMAIL =  CASE WHEN ADPostedEmail = 'Y' THEN 'Y' ELSE 'N' END FROM dbo.Client WHERE ID = @CLIENTID
END
END

/* IF ACCOUNT IS LIVE PROCESS A NUMBER OF STATEMENTS */
IF @ACCOUNTLIVE = 'Y'
BEGIN

/*Determine the account type - token(Y) or normal(N) Y/N */
SELECT @ACCOUNTTYPE = CASE WHEN AccountType = 'a' THEN 'Y' ELSE 'N' END FROM dbo.Client WHERE ID = @CLIENTID

/*Now that we know that the User details are correct, have determined the correct site details and that the client account is live, check to see if this is a duplicate advert, if so expire existing adverts */
IF Exists (Select JBAID from dbo.JBAdvert WHERE JBASiteID = @SiteID AND EmployeeID = @EMPLOYEEID AND JBACategory = @category AND JBALocation = @LOCATION AND JBATitle = @jobtitle AND JBAReference = @reference)
BEGIN
Insert into JBExpiredAdvert(
JBAID, 
JBASiteID, 
JBATitle, 
JBADescription, 
JBAPayRate, 
JBALocation, 
JBACategory, 
JBAEmplymentType,
JBAReference,
JBAStartDate, 
JBADatePosted, 
JBAConkersEmail, 
JBAFeaturedJob, 
JBAOverWrite,
EmployeeID,
ClientID,
[Hours],
Recruiter,
Phone)
Select 
JBAID, 
JBASiteID, 
JBATitle, 
JBADescription, 
JBAPayRate, 
JBALocation, 
JBACategory, 
JBAEmplymentType,
JBAReference,
JBAStartDate, 
JBADatePosted, 
JBAConkersEmail, 
JBAFeaturedJob, 
JBAOverWrite,
EmployeeID,
ClientID,
[Hours],
Recruiter,
Phone
from dbo.JBAdvert WHERE JBASiteID = @SiteID AND EmployeeID = @EMPLOYEEID AND JBACategory = @category AND JBALocation = @LOCATION AND JBATitle = @jobtitle AND JBAReference = @reference
DELETE from dbo.JBAdvert WHERE JBASiteID = @SiteID AND EmployeeID = @EMPLOYEEID AND JBACategory = @category AND JBALocation = @LOCATION AND JBATitle = @jobtitle AND JBAReference = @reference
END

/*Now Process the insert */
INSERT INTO dbo.JBAdvert (
JBASiteID, 
JBATitle, 
JBADescription, 
JBAPayRate, 
JBALocation, 
JBACategory, 
JBAEmplymentType, 
JBAReference, 
JBAStartDate, 
JBAPostFor, 
JBAConkersEmail,
JBAFeaturedJob,
JBAOverWrite,
EmployeeID,
ClientID,
[Hours],
Recruiter,
Phone
)
Values (
@SiteID,
NetSolva.CleanHTML(@jobtitle), 
NetSolva.CleanHTMLAtt(@description), 
NetSolva.CleanHTML(@payrate), 
@LOCATION, 
@category, 
@employmenttype, 
NetSolva.CleanHTML(@reference), 
@startdate, 
'28', 
@altemail,
@ACCOUNTTYPE,
'N',
@EmployeeID,
@ClientID,
@Hours,
NetSolva.CleanHTML(@name),
NetSolva.CleanHTML(@phone))
SET @INSERTEDID = SCOPE_IDENTITY()

/* Now if the client is a token account, deduct a token */
IF @ACCOUNTTYPE = 'Y'
BEGIN
update dbo.Client
Set Tokens = (Select SUM(Tokens - 1) From dbo.Client 
Where ID = @CLIENTID)
Where ID = @CLIENTID
END

/*Having done the insert count the number of jobseekers that have jobs by email alerts that match this job and store this information in dbo.JBESent */
IF @INSERTEDID <> NULL
BEGIN
IF EXISTS(SELECT JBENotificationID FROM dbo.JBEmailNotification WHERE JBENsiteID = @SiteID AND JBENlocation = @LOCATION AND JBENcategory = @category)
BEGIN
INSERT INTO dbo.JBESent (JBESJobID, JBESSiteID, JBESLocation, JBESCategory, JBESReceipients)
Select Top (1)
AD.JBAID,
AD.JBASiteID, 
AD.JBALocation, 
AD.JBACategory,
(
Select 
Count(distinct EN.JBENcandidateID) 
from dbo.JBEmailNotification EN 
Where JBENsiteID = @SiteID AND JBENlocation = @LOCATION AND JBENcategory = @category 
) 
FROM dbo.JBAdvert AD 
Where AD.JBAID = @INSERTEDID
END
END

/*Select parameters AND CLOSE IF ACCOUNT LIVE LINE 129 130 */
SELECT
@PostResult = 20,
@CLIENTURL = @SITEURL +'/coms/em/jobposted.asp?ID=' + @INSERTEDID,
@ADMINURL = null
END

/*Return Data to the ASP Page to Process and Close SP */
SELECT
@PostResult as PostResult,
@CLIENTURL as CLIENTURL,
@ADMINURL as ADMINURL,
@CLIENTEMAIL as CLIENTEMAIL,
@SiteID as SiteID, 
@SITEURL as SITEURL,
@SITEURLSHORT as SITEURLSHORT, 
@SITENOREPLY as SITENOREPLY,
@SITEEMAIL as SITEEMAIL,
@INSERTEDID as JobID
END
GO

Select allOpen in new window

So the end result should be, no data is inserted and a '@PostResult' value of 0, 19, 15, 17 or 18 is returned. If 15, 17 or 18 are returned additional parameters are sent -

@CLIENTURL
@ADMINURL

If none of the above is true, the information is correct so -

First check for duplicate adverts and if exist delete these, then process the insert using a UDF to remove invalid characters

Run some other statements before returning the following information to the ASP page -

@PostResult = 20 as PostResult,
@CLIENTURL = @SITEURL +'/coms/em/jobposted.asp?ID=' + @INSERTEDID as CLIENTURL,
@ADMINURL = null as ADMINURL,
@CLIENTEMAIL as CLIENTEMAIL,
@SiteID as SiteID,
@SITEURL as SITEURL,
@SITEURLSHORT as SITEURLSHORT,
@SITENOREPLY as SITENOREPLY,
@SITEEMAIL as SITEEMAIL,
@INSERTEDID as JobID

The ASP page then displays the result to the user and depending upon @PostResult generates an email confirming the result.

Grateful again if you could let me know if you see any pitfulls in my code, or indeed sugget a better leaner quicker way of doing this...

Thank you