Q: How do I declare a Stored Procedure’s parameter as NOT NULL ?
A: You can’t.
Remarks in the CREATE PROCEDURE t-sql documentation states:
Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.