SQL Server 2008的存储过程中的问题 [英] problem in stored procedure of sql server 2008

问题描述

大家好
我对以下具有"like"功能的存储过程有疑问.
此过程会产生类似"incoreect sintext near 9"的错误
所以请帮帮我

创建过程[dbo].[RptQuarterlistbysalesArea]
@intFilterType AS INT，
@strIDs AS varchar(200)
如
声明@strQuery Varchar(max)
声明@strFilterQuery Varchar(2000)
if(@ intFilterType = 1)
开始
set @ strQuery =''选择dbo.Supervisor.SupervisorName，dbo.Supervisor.ArbSupervisorName，dbo.RouteMaster.RouteCode，dbo.RouteMaster.RouteName，
dbo.RouteMaster.ArbRouteName，dbo.RouteMaster.AlternateRouteCode，dbo.DepotMaster.DepotName，dbo.DepotMaster.ArbDepotName，
dbo.AreaMaster.AreaName，dbo.AreaMaster.ArbAreaName，dbo.AreaMaster.AreaCode，dbo.DepotMaster.AlternateDepotCode
来自dbo.Country INNER JOIN
dbo.RegionMaster开启dbo.Country.CountryCode = dbo.RegionMaster.CountryCode内联接
dbo.DepotMaster开启dbo.RegionMaster.RegionMstCode = dbo.DepotMaster.RegionMstCode内联接
dbo.AreaMaster开启dbo.DepotMaster.DepotCode = dbo.AreaMaster.DepotCode内联接
dbo.SubAreaMaster开启dbo.AreaMaster.AreaCode = dbo.SubAreaMaster.AreaCode INNER JOIN
dbo.RouteMaster ON dbo.SubAreaMaster.SubAreaCode = dbo.RouteMaster.SubAreaCode INNER JOIN
dbo.Supervisor开启dbo.SubAreaMaster.SupervisorCode = dbo.Supervisor.SupervisorCode INNER JOIN
dbo.Company ON dbo.Country.CmpyCode = dbo.Company.CmpyCode''
设置@ strFilterQuery =''WHERE(LEN(dbo.RouteMaster.RouteCode)= 8)或(dbo.RouteMaster.RouteCode LIKE``9％''))和
(dbo.Country.CountryCode IN(''+ @ strIDs +''))''
END
if(@ intFilterType = 2)
开始
设置@ strQuery =''SELECT dbo.Supervisor.SupervisorName，dbo.Supervisor.ArbSupervisorName，dbo.RouteMaster.RouteCode，dbo.RouteMaster.RouteName，
dbo.RouteMaster.ArbRouteName，dbo.RouteMaster.AlternateRouteCode，dbo.DepotMaster.DepotName，dbo.DepotMaster.ArbDepotName，
dbo.AreaMaster.AreaName，dbo.AreaMaster.ArbAreaName，dbo.AreaMaster.AreaCode，dbo.DepotMaster.AlternateDepotCode
来自dbo.Country INNER JOIN
dbo.RegionMaster开启dbo.Country.CountryCode = dbo.RegionMaster.CountryCode内联接
dbo.DepotMaster开启dbo.RegionMaster.RegionMstCode = dbo.DepotMaster.RegionMstCode内联接
dbo.AreaMaster开启dbo.DepotMaster.DepotCode = dbo.AreaMaster.DepotCode内联接
dbo.SubAreaMaster开启dbo.AreaMaster.AreaCode = dbo.SubAreaMaster.AreaCode INNER JOIN
dbo.RouteMaster ON dbo.SubAreaMaster.SubAreaCode = dbo.RouteMaster.SubAreaCode INNER JOIN
dbo.Supervisor开启dbo.SubAreaMaster.SupervisorCode = dbo.Supervisor.SupervisorCode INNER JOIN
dbo.Company ON dbo.Country.CmpyCode = dbo.Company.CmpyCode''
设置@ strFilterQuery =''WHERE(LEN(dbo.RouteMaster.RouteCode)= 8)或(dbo.RouteMaster.RouteCode LIKE``9％''))和
(dbo.DepotMaster.DepotCode IN(''+ @ strIDs +''))''
END
设置@ strQuery = @ strQuery + @ strFilterQuery
打印(@strQuery)
exec(@strFilterQuery)

Hi Every one
I have problem with below stored procedure with ''like'' function .
this procedure give the error like "incoreect sintext near 9"
so please help me

CREATE PROCEDURE [dbo].[RptQuarterlistbysalesArea]
@intFilterType AS INT ,
@strIDs AS varchar(200)
as
declare @strQuery Varchar(max)
declare @strFilterQuery Varchar(2000)
if(@intFilterType=1)
begin
set @strQuery= '' SELECT dbo.Supervisor.SupervisorName, dbo.Supervisor.ArbSupervisorName, dbo.RouteMaster.RouteCode, dbo.RouteMaster.RouteName,
dbo.RouteMaster.ArbRouteName, dbo.RouteMaster.AlternateRouteCode, dbo.DepotMaster.DepotName, dbo.DepotMaster.ArbDepotName,
dbo.AreaMaster.AreaName, dbo.AreaMaster.ArbAreaName, dbo.AreaMaster.AreaCode, dbo.DepotMaster.AlternateDepotCode
FROM dbo.Country INNER JOIN
dbo.RegionMaster ON dbo.Country.CountryCode = dbo.RegionMaster.CountryCode INNER JOIN
dbo.DepotMaster ON dbo.RegionMaster.RegionMstCode = dbo.DepotMaster.RegionMstCode INNER JOIN
dbo.AreaMaster ON dbo.DepotMaster.DepotCode = dbo.AreaMaster.DepotCode INNER JOIN
dbo.SubAreaMaster ON dbo.AreaMaster.AreaCode = dbo.SubAreaMaster.AreaCode INNER JOIN
dbo.RouteMaster ON dbo.SubAreaMaster.SubAreaCode = dbo.RouteMaster.SubAreaCode INNER JOIN
dbo.Supervisor ON dbo.SubAreaMaster.SupervisorCode = dbo.Supervisor.SupervisorCode INNER JOIN
dbo.Company ON dbo.Country.CmpyCode = dbo.Company.CmpyCode''
set @strFilterQuery='' WHERE (LEN(dbo.RouteMaster.RouteCode) = 8)or(dbo.RouteMaster.RouteCode LIKE ''9%'')) and
(dbo.Country.CountryCode IN (''+@strIDs+''))''
END
if(@intFilterType=2)
begin
set @strQuery='' SELECT dbo.Supervisor.SupervisorName, dbo.Supervisor.ArbSupervisorName, dbo.RouteMaster.RouteCode, dbo.RouteMaster.RouteName,
dbo.RouteMaster.ArbRouteName, dbo.RouteMaster.AlternateRouteCode, dbo.DepotMaster.DepotName, dbo.DepotMaster.ArbDepotName,
dbo.AreaMaster.AreaName, dbo.AreaMaster.ArbAreaName, dbo.AreaMaster.AreaCode, dbo.DepotMaster.AlternateDepotCode
FROM dbo.Country INNER JOIN
dbo.RegionMaster ON dbo.Country.CountryCode = dbo.RegionMaster.CountryCode INNER JOIN
dbo.DepotMaster ON dbo.RegionMaster.RegionMstCode = dbo.DepotMaster.RegionMstCode INNER JOIN
dbo.AreaMaster ON dbo.DepotMaster.DepotCode = dbo.AreaMaster.DepotCode INNER JOIN
dbo.SubAreaMaster ON dbo.AreaMaster.AreaCode = dbo.SubAreaMaster.AreaCode INNER JOIN
dbo.RouteMaster ON dbo.SubAreaMaster.SubAreaCode = dbo.RouteMaster.SubAreaCode INNER JOIN
dbo.Supervisor ON dbo.SubAreaMaster.SupervisorCode = dbo.Supervisor.SupervisorCode INNER JOIN
dbo.Company ON dbo.Country.CmpyCode = dbo.Company.CmpyCode''
set @strFilterQuery='' WHERE (LEN(dbo.RouteMaster.RouteCode) = 8)or(dbo.RouteMaster.RouteCode LIKE ''9%'')) and
(dbo.DepotMaster.DepotCode IN (''+@strIDs+''))''
END
Set @strQuery=@strQuery+@strFilterQuery
print(@strQuery)
exec (@strFilterQuery)

推荐答案

认为您需要将双引号加倍，例如:

Think you need to double the quotes like:

set @strFilterQuery=' WHERE (LEN(dbo.RouteMaster.RouteCode) = 8)or(dbo.RouteMaster.RouteCode LIKE ''9%'')) and
(dbo.DepotMaster.DepotCode IN ('+@strIDs+'))'

这篇关于SQL Server 2008的存储过程中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！