• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Stored Procedure Sense Check Please

Hello Experts,

I would be grateful for a quick sence check on the following stored procedure, fundamentally it is an insert statement, however not all of the variables are supplied in the feed to the page, so it firstly needs to do a select statement, to grab some of the information that needs to be inserted.

Appreciate it if you could have a quick read, and sense check, maybe even improve if possible -

thank you
CREATE PROCEDURE [dbo].[JobboardBBinsert]
@LocationID int,
@JobTitle nvarchar(50),
@Description nvarchar(max),
@Payrate nvarchar(250),
@category nvarchar(50),
@employmenttype nvarchar(50),
@reference nvarchar(250),
@startdate nvarchar(50),
@username nvarchar(255),
@conkermail nvarchar(255),
@password nvarchar(50)
AS
Select E.JBEID, E.JBESiteID, L.JBLocation, C.JBCLID
From dbo.JBEmployee E 
inner join dbo.JBLocation L on L.JBLSiteID = E.JBESiteID
inner join dbo.JBClient C on C.JBCLID = E.JBEClientID 
Where L.JBLID = @LocationID AND E.JBEUsername = @username AND E.JBEPassword = @password

SET @EmployeeId = E.JBEID, @SiteID = E.JBESiteID, @ClientId = C.JBCLID, @location = L.JBLocation

INSERT INTO dbo.JBAdvert (JBASiteID, JBAEmployeeID, JBAClientID, JBATitle, JBADescription, JBAPayRate, JBALocation, JBACategory, JBAEmplymentType, JBAReference, JBAStartDate, JBAPostFor, JBAConkersEmail)
VALUES (@SiteID, @EmployeeId, @ClientId, @JobTitle, @Description, @Payrate, @location, @category, @employmenttype, @reference, @startdate, 28, @conkermail)

Open in new window

0
garethtnash
Asked:
garethtnash
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Select E.JBEID, E.JBESiteID, L.JBLocation, C.JBCLID
From dbo.JBEmployee E
inner join dbo.JBLocation L on L.JBLSiteID = E.JBESiteID
inner join dbo.JBClient C on C.JBCLID = E.JBEClientID
Where L.JBLID = @LocationID AND E.JBEUsername = @username AND E.JBEPassword = @password

SET @EmployeeId = E.JBEID, @SiteID = E.JBESiteID, @ClientId = C.JBCLID, @location = L.JBLocation

would be :


Select @EmployeeId = E.JBEID
, @SiteID = E.JBESiteID
, @location = L.JBLocation
, @ClientId = C.JBCLID
From dbo.JBEmployee E
inner join dbo.JBLocation L on L.JBLSiteID = E.JBESiteID
inner join dbo.JBClient C on C.JBCLID = E.JBEClientID
Where L.JBLID = @LocationID AND E.JBEUsername = @username AND E.JBEPassword = @password

0
 
garethtnashAuthor Commented:
Thanks Angellll, this gives me the following error -

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@EmployeeId".
appreciate your thoughts
0
 
tigin44Commented:
use this
CREATE PROCEDURE [dbo].[JobboardBBinsert]
	@LocationID int,
	@JobTitle nvarchar(50),
	@Description nvarchar(max),
	@Payrate nvarchar(250),
	@category nvarchar(50),
	@employmenttype nvarchar(50),
	@reference nvarchar(250),
	@startdate nvarchar(50),
	@username nvarchar(255),
	@conkermail nvarchar(255),
	@password nvarchar(50)
AS

	INSERT INTO dbo.JBAdvert (JBASiteID, JBAEmployeeID, JBAClientID, JBATitle, JBADescription, JBAPayRate, JBALocation, JBACategory, JBAEmplymentType, JBAReference, JBAStartDate, JBAPostFor, JBAConkersEmail)
	Select E.JBESiteID, E.JBEID, C.JBCLID, @JobTitle, @Description, @Payrate,  L.JBLocation, @category, @employmenttype, @reference, @startdate, 28, @conkermail
	From dbo.JBEmployee E 
		inner join dbo.JBLocation L on L.JBLSiteID = E.JBESiteID
		inner join dbo.JBClient C on C.JBCLID = E.JBEClientID 
	Where L.JBLID = @LocationID 
	  AND E.JBEUsername = @username 
	  AND E.JBEPassword = @password

Open in new window

0
 
garethtnashAuthor Commented:
Very neat - thank you
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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