I'm getting this error in my SP and I understand why I'm getting it...just not sure how to rectify it without using dynamic sql.
The error that I'm getting is;
"Converion failed when converting the varchar value ' AND A.accredited <> 0' to data type Int".
Here is my SP:
ALTER PROCEDURE [dbo].[spLoadEquipmentCalibrationGrid]
SET NOCOUNT ON;
DECLARE @Accredited VARCHAR(50)=NULL
IF @useAccredited <> 0
SET @Accredited = ' AND A.accredited <> 0'
IF @type_ID = 0
SELECT CONVERT(bit,0) AS 'Selected',
A.equipment_ID AS 'Equipment ID',
B.equipmentType AS 'Type',
C.equipmentName AS 'Name',
CONVERT(bit,A.accredited) AS 'Accredited',
F.department AS 'Department',
REPLACE(G.calibrationHouse,'~','''') AS 'Calibration House',
CONVERT(bit,A.active) AS 'Active',
D.userAccount AS 'Created By',
D.dateCreated AS 'Date Created',
E.userAccount AS 'Updated By',
e.dateUpdated AS 'Date Updated'
FROM HEADER_Equipment A LEFT JOIN LKUP_EquipmentTypes B ON A.equipmentType_ID = B.equipmentType_ID
LEFT JOIN LKUP_EquipmentNames C ON A.equipmentName_ID = C.equipmentName_ID
LEFT JOIN LKUP_UserAccounts D ON A.createdBy_ID = D.userAccount_ID
LEFT JOIN LKUP_UserAccounts E ON A.updatedBy_ID = E.userAccount_ID
LEFT JOIN LKUP_Departments F ON A.department_ID = F.department_ID
LEFT JOIN LKUP_CalibrationHouses G ON A.calibrationHouse_ID = G.calibrationHouse_ID
WHERE A.equipmentType_ID >= 0 + @Accredited