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

Open 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
garethtnashAsked:
Who is Participating?
 
sameer_goyalConnect With a Mentor Commented:
since @SiteId and @EmployeeId is an integer, you will have to convert it to varchar first to be able to append it to the URL

TRy this

@CLIENTURL = @SITEURL +'/coms/mp/mpfailure.asp?ID=3&SID=' + Convert(varchar(50),@SiteID) + '&UID=' + Convert(varchar(50),@EMPLOYEEID) + '&jobtitle=',

hope it helps
0
 
sameer_goyalCommented:
I think what you are doing is good. Since, your processing depends on a number of parameters, I think this is a good way to achieve the end result.
0
 
garethtnashAuthor Commented:
Ummm,

I've just tested it, and it failed, it returned null values for --

@PostResult as PostResult,
@CLIENTURL as CLIENTURL,
@ADMINURL as ADMINURL,
@CLIENTEMAIL as CLIENTEMAIL,
@INSERTEDID as JobID

And nothing was inserted into the database?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
sameer_goyalCommented:
I just looked at it from the logical point of view.

What is the error you are getting? Moreover, where did you try this, thru ASP.NET or in Sql server mgmt studio?
0
 
garethtnashAuthor Commented:
SSMS it isn't returning values for -

@PostResult as PostResult,
@CLIENTURL as CLIENTURL,
@ADMINURL as ADMINURL,
@CLIENTEMAIL as CLIENTEMAIL,
@INSERTEDID as JobID

But does return values for -

@SiteID as SiteID,
@SITEURL as SITEURL,
@SITEURLSHORT as SITEURLSHORT,
@SITENOREPLY as SITENOREPLY,
@SITEEMAIL as SITEEMAIL,

I'm just going through line by line -

:(
0
 
sameer_goyalCommented:
looks good to me though. can you send me your tables schema and I can try an explore it
0
 
garethtnashAuthor Commented:
Hi Sameer,

Think I've just got to the bottom of this, but now I'm getting the following error --

Msg 245, Level 16, State 1, Line 90
Conversion failed when converting the nvarchar value 'http://www.development.hampshirejobsonline.co.uk/coms/mp/mpfailure.asp?ID=3&SID=' to data type int.

Where line 90 is -

@CLIENTURL = @SITEURL +'/coms/mp/mpfailure.asp?ID=3&SID=' + @SiteID + '&UID=' + @EMPLOYEEID + '&jobtitle=',


Any suggestion?

Thank you
0
 
garethtnashAuthor Commented:
Thanks Sameer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.