错误“必须声明标量变量”在我的程序中 [英] error "Must declare the scalar variable " in my procedure

问题描述

当我执行我的存储过程时，我收到错误..必须声明标量变量@serviceCode..

我的程序是

 ALTER PROC [dbo]。[Clinic_ServiceInsuranceList] 
 @serviceCode INT，
 @InsuranceID INT 
 AS 
 BEGIN 
声明@sqlTextAllCount nvarchar（max）; 
 
 set @sqlTextAllCount ='
 SELECT dbo.Service.Code，dbo.Service.Title，dbo.ActCostService.Cost，dbo.InsuranceServiceCost.Cost 
 FROM dbo.Service， dbo.ActCostService，dbo.InsuranceServiceCost，dbo.InsuraceCost 
 WHERE 
 [dbo]。[服务] .ID = @ serviceCode AND 
 --dbo.InsuraceCost.InsuraceID=@InsuranceID AND 
 dbo.ActCostService.ServiceID=@serviceCode AND 
 dbo.InsuranceServiceCost.ServiceID=@serviceCode'
 
 
 if（@InsuranceID<> 0）
设置@sqlTextAllCount + ='和InsuraceCost.InsuraceID ='+ convert（nvarchar，@ InsuranceID）
执行sp_executesql @sqlTextAllCount 
 END 

解决方案

试试这个代码

 ALTER PROC [dbo]。[Clinic_ServiceInsuranceList] 
 @serviceCode INT，
 @InsuranceID INT 
 AS 
 BEGIN 
声明@sqlTextAllCount nvarchar（max）; 
 set @sqlTextAllCount ='
 SELECT dbo.Service.Code，dbo.Service.Title，dbo.ActCostService.Cost，dbo.InsuranceServiceCost.Cost 
 FROM dbo.Service，dbo.ActCostService， dbo.InsuranceServiceCost，dbo.InsuraceCost 
 WHERE 
 [dbo]。[服务] .ID ='+ convert（nvarchar（10），@ serviceCode）+'AND 
 dbo.ActCostService.ServiceID ='+ convert（nvarchar（10），@ serviceCode）+'AND 
 dbo.InsuranceServiceCost.ServiceID ='+ convert（nvarchar（10），@ serviceCode）+''
 
 
 if（@InsuranceID<> 0）
设置@sqlTextAllCount + ='和InsuraceCost.InsuraceID ='+ convert（nvarchar，@ InsuranceID）
 print @sqlTextAllCount 
执行sp_executesql @sqlTextAllCount 
 END 

尝试使用

  set  @sqlTextAllCount = '  
 SELECT dbo .Service.Code，dbo.Service.Title，dbo.ActCostService.Cost，dbo.InsuranceServiceCost.Cost 
来自dbo.Service，dbo.ActCostService，dbo.InsuranceServiceCost，dbo.InsuraceCost 
 WHERE 
 [dbo]。[服务] .ID = @ serviceCode AND 
 --dbo。 InsuraceCost.InsuraceID =' + convert（ nvarchar ， @ InsuranceID ）+   AND 
 dbo.ActCostService.ServiceID =' + convert（ nvarchar ， @ serviceCode ）+ '  AND 
 dbo.InsuranceServiceCost.ServiceID =' + convert（ nvarchar ， @ serviceCode  ）

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

ALTER PROC [dbo].[Clinic_ServiceInsuranceList]
@serviceCode INT ,
@InsuranceID INT 
AS
BEGIN
declare @sqlTextAllCount nvarchar(max);
	
set @sqlTextAllCount='
SELECT dbo.Service.Code,dbo.Service.Title,dbo.ActCostService.Cost,dbo.InsuranceServiceCost.Cost
 FROM dbo.Service,dbo.ActCostService,dbo.InsuranceServiceCost,dbo.InsuraceCost
WHERE
[dbo].[Service].ID=@serviceCode AND
--dbo.InsuraceCost.InsuraceID=@InsuranceID AND
dbo.ActCostService.ServiceID=@serviceCode AND
dbo.InsuranceServiceCost.ServiceID=@serviceCode '


if(@InsuranceID <>0)
			Set @sqlTextAllCount += ' And InsuraceCost.InsuraceID = '    +  convert(nvarchar,@InsuranceID)
Execute sp_executesql @sqlTextAllCount
END

解决方案

Try this code

ALTER PROC [dbo].[Clinic_ServiceInsuranceList]
@serviceCode INT ,
@InsuranceID INT 
AS
BEGIN
declare @sqlTextAllCount nvarchar(max);
set @sqlTextAllCount='
SELECT dbo.Service.Code,dbo.Service.Title,dbo.ActCostService.Cost,dbo.InsuranceServiceCost.Cost
 FROM dbo.Service,dbo.ActCostService,dbo.InsuranceServiceCost,dbo.InsuraceCost
WHERE
[dbo].[Service].ID='+convert(nvarchar(10),@serviceCode)+' AND
dbo.ActCostService.ServiceID='+convert(nvarchar(10),@serviceCode)+' AND
dbo.InsuranceServiceCost.ServiceID='+convert(nvarchar(10),@serviceCode) +''
 

if(@InsuranceID <>0)
			Set @sqlTextAllCount += ' And InsuraceCost.InsuraceID = '    +  convert(nvarchar,@InsuranceID)
print 	@sqlTextAllCount
Execute sp_executesql @sqlTextAllCount
END

try with

set @sqlTextAllCount='
SELECT dbo.Service.Code,dbo.Service.Title,dbo.ActCostService.Cost,dbo.InsuranceServiceCost.Cost
 FROM dbo.Service,dbo.ActCostService,dbo.InsuranceServiceCost,dbo.InsuraceCost
WHERE
[dbo].[Service].ID=@serviceCode AND
--dbo.InsuraceCost.InsuraceID='+convert(nvarchar,@InsuranceID)+' AND
dbo.ActCostService.ServiceID='+convert(nvarchar,@serviceCode)+' AND
dbo.InsuranceServiceCost.ServiceID='+convert(nvarchar,@serviceCode)

这篇关于错误“必须声明标量变量”在我的程序中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！