必须声明标量变量"@cid".

问题描述

当我执行存储过程时，出现错误"..必须声明标量变量"@cid" ..".
我的程序是

when i am executing my stored procedure i got the error "..Must declare the scalar variable "@cid" .."
my procedure is

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 @lquery varchar(5000)

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

INSERT INTO @TEMPDIAG

EXEC (@lQuery)



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

推荐答案

declare @lquery varchar(5000)

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


INSERT INTO @TEMPDIAG

EXEC (@lQuery)

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

希望这会有所帮助，如果是，那么请接受并投票答复.任何对此的任何疑问/问题都将受到欢迎.

谢谢&问候
RDBurmon.Sr.软件工程师

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

这篇关于必须声明标量变量"@cid".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！