Must declare the scalar variable "@cid".

hi,

Check the variable name and the datatype

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 

ALTER PROCEDURE [dbo].[Sample]
@cid varchar
AS
BEGIN
 
    DECLARE @TEMPDIAG TABLE (SalName VARCHAR(255),Amount decimal(18,2))
 
 DECLARE @ParmDefinition NVARCHAR(500)
declare @lquery Nvarchar(MAX)
 
set @lquery='select SalName,Amount from tbl_SalaryAssign where CompanyID=@cid'
 
 SET @ParmDefinition = N'@cid varchar'
 
INSERT INTO @TEMPDIAG
EXECUTE sp_executesql
@lQuery,
@ParmDefinition,
@cid = @cid
 
DECLARE @TEMPDIAG1 TABLE ( Workingtype VARCHAR(255),Count1 int)
 
declare @lquery1 varchar(5000)
 
set @lquery1='select o.Wrkngtype,count(Wrkngtype) as NoOfDays from tbl_DailyExpense o
inner join tbl_SalaryAssign od on o.Wrkngtype = od.SalName  group by
  o.Wrkngtype'
 
INSERT INTO @TEMPDIAG1
 
EXEC (@lQuery1)
 

select o.Workingtype,o.Count1 as NoOfDays,od.Amount from @TEMPDIAG1 o
inner join @TEMPDIAG od on o.Workingtype = od.SalName
  -- 153Ms  as opposed to 653Ms without the hint

 
END

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

The kishorekke's answer is almost perfect, but need some explonations.

The below line is the reason of error:

set @lquery='select SalName,Amount from tbl_SalaryAssign where CompanyID=@cid'

The input parameter: @cid as VARCHAR for stored procedure is declared but it's never used. Why? read the kishorekke's answer.

I see 2 additional, potential reasons of problems.
1)
VARCHAR without size stores default only 1 sign.
Use: @cid VARCHAR(30)
2)
The CompanyID field stores probably integer values.
What i'm trying to explain you, is: '1' is not equal to 1
If i'm right, the @cid must be an integer variable.