Below is my code snippet of a test case for a stored procedure my_proc to check not null parameter passed to the stored procedure.
--faking required tables here
--set values of parameters to be passed
DECLARE @LINK_ID INT = null -- required parameter which is passed null
, @LINK_TYPE VARCHAR(250) = 'GTN_ALERT_OPERATIONS'
, @FILE_NAME VARCHAR(250) = 'XML.png'
, @FILE_SIZE VARCHAR(250) = '2060'
, @FILE_DATA VARBINARY(MAX) = null
, @FILE_TYPE VARCHAR(10) = 'PNG'
, @MODUKE_KEY VARCHAR(20) ='GTN'
, @USER_ID INT = 1
, @ORGANIZATION_ID INT = 1
, @UPDATEDATE datetime = getdate()
, @ERR_STATUS SMALLINT = 0
, @ERR_MSG VARCHAR(MAX) = ''
, @ERR_STATUS_EXPECTED SMALLINT = 2
EXEC tsqlt.ExpectException @ExpectedMessagePattern ='%Cannot insert the value NULL into column ''LINK_ID''%'
BEGIN TRY
EXEC my_proc @LINK_ID = @LINK_ID, @LINK_TYPE = @LINK_TYPE, @FILE_NAME = @FILE_NAME, @FILE_SIZE = @FILE_SIZE
, @FILE_DATA = @FILE_DATA, @FILE_TYPE = @FILE_TYPE, @MODUKE_KEY = @MODUKE_KEY, @USER_ID = @USER_ID
, @ORGANIZATION_ID = @ORGANIZATION_ID, @ERR_STATUS = @ERR_STATUS OUT, @ERR_MSG = @ERR_MSG OUT
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
begin
BEGIN TRAN --reopen an transaction as it gave error due to rollack called in my_proc
end
END CATCH
If here @LINK_ID is not nullable parameter so it should throw exception like
Cannot insert null ..
as I have mentioned above. So here it works well and results as passed.
But even if I change some random message instead of "cannot insert null.." this test passes, which is ideally wrong. It should fail.
It happens due to internal transaction used in my_proc, which executes rollback tran on error.
Please suggest a possible solution.
Finally I got answer to my problem which will helpful for tsqlt test case and normal nested transactions procedures