I have the following table:
CREATE TABLE NZQA_Unit(
NZQAUnitID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
NZQAUnitNumber int NOT NULL,
Title nvarchar(255) NOT NULL,
Level smallint NOT NULL,
Credits smallint NOT NULL,
Classification nvarchar(255) NULL,
AvailableGrade int NULL DEFAULT 1,
Purpose nvarchar(max) NULL,
Validity int NOT NULL DEFAULT 1, -- by default the unit will be 'Current'
Document VARBINARY(MAX) NULL, -- for being able to upload a PDF or Word document
DocumentExtension varchar(5) NULL, -- for storing the file extension
CONSTRAINT AK_NZQA_Unit_Title UNIQUE(Title),
CONSTRAINT AK_NZQA_Unit_Number UNIQUE(NZQAUnitNumber),
CONSTRAINT FK_NZQA_Unit_Validity FOREIGN KEY (Validity) REFERENCES NZQA_Unit_Validity (ValidityID),
CONSTRAINT FK_NZQA_Unit_AvailableGrade FOREIGN KEY (AvailableGrade) REFERENCES NZQA_Unit_Assessment_Grade (AssessmentGradeID),
CONSTRAINT CK_NZQA_Unit_Title CHECK ((len(ltrim(rtrim(Title)))>(2))),
CONSTRAINT CK_NZQA_Unit_ID_Range CHECK (NZQAUnitNumber >= 1000 AND NZQAUnitNumber <= 99999), --Inclusive
CONSTRAINT CK_NZQA_Unit_Level CHECK (Level >= 1 AND Level <= 10), -- Level must be between 1 and 10 https://www.nzqa.govt.nz/studying-in-new-zealand/understand-nz-quals/
CONSTRAINT CK_NZQA_Unit_Credits CHECK (Credits >= 1 AND Credits <= 999), -- 999 has been set arbitrarily but it's high enough to fit an Engineering Degree
CONSTRAINT CK_NZQA_Unit_DocumentSize CHECK (DATALENGTH(Document) <= 524288), -- Maximum size 500 KB https://stackoverflow.com/questions/34741079/can-i-set-2-mb-for-maximum-size-of-varbinary https://www.gbmb.org/mb-to-bytes
CONSTRAINT CK_NZQA_Unit_DocumentExtension CHECK (DocumentExtension IN ('.pdf', '.doc', '.docx')) -- this check is not case sensitive, i.e. '.DOCX' won't trigger an error
);
GO
And I'm writing the following stored procedure:
DROP PROCEDURE IF EXISTS Modify_NZQA_Unit
GO
CREATE PROCEDURE Modify_NZQA_Unit
@NZQAUnitID int,
@NZQAUnitNumber int,
@Title nvarchar(255),
@Level smallint,
@Credits smallint,
@Classification nvarchar(255) NULL,
@AvailableGrade int,
@Purpose nvarchar(max),
@Validity int,
@Document VARBINARY(MAX),
@DocumentExtension varchar(5),
@overwriteFile bit -- 1 to overwrite, 0 to no overwrite
AS
BEGIN
IF (@NZQAUnitID IS NULL)
BEGIN
THROW 51006, 'You must input the NZQA identifier (NZQAUnitID)', 1;
END
SET @Title = Replace(@Title, '''', '''''') -- singles quotes must be escaped
SET @Classification = Replace(@Classification, '''', '''''')
SET @Purpose = Replace(@Purpose, '''', '''''')
SET @DocumentExtension = Replace(@DocumentExtension, '''', '''''')
DECLARE @updateStatement AS NVARCHAR(1000);
SET @updateStatement = 'UPDATE NZQA_Unit SET NZQAUnitNumber = '+CONVERT(VARCHAR, @NZQAUnitNumber)+', Title = '''+@Title+''', Level = '+CONVERT(VARCHAR, @Level)+', Credits = '+CONVERT(VARCHAR, @Credits)+', Classification = '''+@Classification+''', AvailableGrade = '+CONVERT(VARCHAR, @AvailableGrade)+', Purpose = '''+@Purpose+''', Validity = '+CONVERT(VARCHAR, @Validity)
IF (@overwriteFile IS NULL)
BEGIN
THROW 51007, 'Variable @overwriteFile cannot be null', 1;
END
ELSE
BEGIN
IF (@overwriteFile = 1)
BEGIN
IF (@Document IS NULL)
BEGIN
THROW 51008, 'If the variable @overwriteFile is set to 1, a file (@Document) must be provided', 1;
END
IF (@DocumentExtension IS NULL)
BEGIN
THROW 51009, 'If the variable @overwriteFile is set to 1, the document extension (@DocumentExtension) must be provided', 1;
END
SET @updateStatement = @updateStatement + ', Document = '+'HERE WILL COME THE VARBINARY'+', DocumentExtension = '''+@DocumentExtension + ''' '
--DOESN'T WORK: EXEC('UPDATE NZQA_Unit SET NZQAUnitNumber = '+@NZQAUnitNumber+', Title = '''+@Title+''', Level = '+@Level+', Credits = '+@Credits+', Classification = '''+@Classification+''', AvailableGrade = '+@AvailableGrade+', Purpose = '''+@Purpose+''', Validity = '+@Validity + ', Document = ' + @document + ', DocumentExtension = '''+@DocumentExtension + ''' ' +' WHERE NZQAUnitID = '+@NZQAUnitID)
UPDATE NZQA_Unit SET NZQAUnitNumber = @NZQAUnitNumber, Title = @Title, Level = @Level, Credits = @Credits, Classification = @Classification, AvailableGrade = @AvailableGrade, Purpose = @Purpose, Validity = @Validity, Document = @document, DocumentExtension = @DocumentExtension WHERE NZQAUnitID = @NZQAUnitID
END
ELSE
BEGIN
UPDATE NZQA_Unit SET NZQAUnitNumber = @NZQAUnitNumber, Title = @Title, Level = @Level, Credits = @Credits, Classification = @Classification, AvailableGrade = @AvailableGrade, Purpose = @Purpose, Validity = @Validity WHERE NZQAUnitID = @NZQAUnitID
END
END
SET @updateStatement = @updateStatement +' WHERE NZQAUnitID = '+CONVERT(VARCHAR, @NZQAUnitID)
PRINT @updateStatement
END
GO
How could I insert the varbinary data (@Document) into the @updateStatement variable? That way I could simply do a EXEC(@updateStatement)?
Code to execute the procedure below:
DECLARE @current AS INT = 1
DECLARE @expiring AS INT = 2
DECLARE @expired AS INT = 3
DECLARE @achieved AS INT = 1
DECLARE @datos AS VARBINARY(30) = CONVERT(varbinary(30), N'this IS a test')
INSERT NZQA_Unit (NZQAUnitNumber, [Title], [Level], [Credits], [Classification], [AvailableGrade], [Purpose], Validity) VALUES (6401, N'Provide first aid', 2, 1, N'Health Studies > First Aid', @achieved, N'People credited with this unit standard are able to provide first aid.', @current)
EXEC Modify_NZQA_Unit 1, 6424, N'Provide first aid', 2, 1, N'Health Studies > First Aid', @achieved, N'People credited with this unit standard are able to provide first aid.', @current, @datos, '.pdf', 1
