Or you can use dynamic SQL:

DECLARE @category VARCHAR(50);
DECLARE @sql VARCHAR(1000);

--Based on category values i should get records dynamically
SET @category = 'lamp';
SET...

I appreciate that this is not using SSMS, but getting this info out in a structured format is just a few clicks away if you create a database project in...

Try this

DROP TABLE IF EXISTS #LoanNumber;

CREATE TABLE #LoanNumber
(
  Loannumber  VARCHAR(10)
, ID    INT IDENTITY(1000, 1)
, GeneratedLoan AS RIGHT('0000000000' + CAST(ID AS VARCHAR(10)),10)
);

--Removed duplicate response

The obvious question: have you rebooted your machine?