[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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
0
garethtnash
Asked:
garethtnash
  • 4
  • 4
1 Solution
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
sameer_goyalCommented:
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
 
garethtnashAuthor Commented:
Thanks Sameer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now