Stored Procedure Variable declaration Assistance

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
ALTER PROC [dbo].[spNC_GetJobBoard]
 @License nvarchar(50) = '',
 @Specialty nvarchar(50) = '',
 @Location nvarchar(50) = '',
 @Type nvarchar(50) = ''
 
 
AS
SET NOCOUNT ON
 
IF OBJECT_ID('tempdb..#tmpJobData') IS NOT NULL
	DROP TABLE #tmpJobData
 
 
IF OBJECT_ID('tempdb..#tmpFacData') IS NOT NULL
	DROP TABLE #tmpFacData
 
SELECT DISTINCT XModID, [value] as Facility, 
(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'C_State' and mask = 1 and XModID = F.XModID) as State,
(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'C_City' and mask = 1 and XModID = F.XModID) as City
INTO #tmpFacData
FROM KB_XMod_Index_VC50 F
WHERE [key] = 'C_CustomerName'
 
SELECT DISTINCT XModID, 
	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_ShiftTime' and mask = 2 and XModID = B.XModID) as ShiftTime,
    (SELECT [value] FROM KB_XMod_Index_VC100 WHERE [key] = 'B_FacilityName' and mask = 1 and XModID = B.XModID) as Facility,
	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_LicenseRequested' and mask = 1 and XModID = B.XModID) as LicReq,
	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_SpecialtyRequested' and mask = 1 and XModID = B.XModID) as SpecReq,
	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Status' and mask = 1 and XModID = B.XModID) as Status,
	(SELECT [value] FROM KB_XMod_Index_VC400 WHERE [key] = 'B_ExternalJobDesc' and mask = 1 and XModID = B.XModID) as JobDesc,
    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_AssignmentLength' and mask = 1 and XModID = B.XModID) as Length
INTO #tmpJobData
FROM KB_XMod_Index_VC50 B
WHERE (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Status' and mask = 1 and XModID = B.XModID) IN ('Per-Diem Job Order', 'Travel Job Order', 'Perm Job Order')
 
UPDATE #tmpJobData
SET Status = 'Travel' WHERE
Status = 'Travel Job Order'
 
UPDATE #tmpJobData
SET Status = 'Per-Diem' WHERE
Status = 'Per-Diem Job Order'
 
UPDATE #tmpJobData
SET Status = 'Perm Plcmt' WHERE
Status = 'Perm Job Order'
 
UPDATE #tmpJobData
SET Length = 'N/A' WHERE
Length IS NULL OR Length = ' '
 
UPDATE #tmpJobData
SET JobDesc = '<font face=verdana size=2>On going Local Per-Diem position</font>' WHERE
JobDesc IS NULL OR JobDesc = ' '
 
SELECT DISTINCT J.XModID, J.ShiftTime, J.Facility, J.LicReq, J.SpecReq, J.Status, J.Length, J.JobDesc, (F.City + ', ' + F.State) as Location, 'ApplyNow/tabid/134/Default.aspx' as LinkData
FROM #tmpJobData J, #tmpFacData F
WHERE J.Facility = F.Facility 
AND J.LicReq = @License 
AND J.Status = @Type
AND J.SpecReq = @Specialty
AND F.State = @Location