- Remove comma from the second variable declared.
- Put braces as defined below.
- There might be case where more than one records are returning.
- Also, check what query needs to be exactly as both select will return n number of records. Use where condition to get exact value.
If you need the variables from db tables only. Then declare it as local variables instead of procedure parameters.
So use this and run and leme know if there is any error.
CREATE PROCEDURE Allocate_call
@comp_no as numeric(10) = (select Top 1 Complaint_no from Call_Reg),
@Tech_name as varchar(50) = (select Top 1 Fname from New_Tech)
insert into Call_allocation values('@comp_no', '@Tech_name')
Hope, this helps.