Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • 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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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