exec失败，因为名称不是有效的标识符？ [英] exec failed because the name not a valid identifier?

问题描述

USE [ABC]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[LibFetchFilterItem]
@Query nvarchar(1000)

AS
Begin
set nocount on

DECLARE @str varchar(8000)

SET @str = N'
  select ID
  ,case  when i.CodeVersion>1 then i.Code+''+.+''+convert(varchar,i.CodeVersion) else i.Code end as Code
  ,i.OldCode
  ,HRMS.dbo.InitialCap(i.[Name]) as Name
  ,i.[Description]
  ,d.DeptName as Department
  ,HRMS.dbo.InitialCap(p.[Name]) as Product
  ,Edition
  ,[Version]
  ,PublisherBrand
  ,Country
  ,KeywordDesc
  ,l.[name] as Location
  ,HRMS.dbo.InitialCap(s.[Name]) as SubCategory
  ,Synopsis
  ,i.Website
  from LIBMstItem i inner join LIBMstLocation l  on i.LocationId=l.LocationId
  inner join LIBMstProduct p on i.ProductCategory = p.Id
  Left outer join  HRMS.dbo.Master_EmployeeMaster u on i.BroughtBy=u.[NewID]
  Left outer join  dynamicreport.dbo.CompanyMst c on i.CompanyId = c.CompanyId
  Left outer join  HRMS.dbo.Master_DepartmentMaster d on i.DepartmentID = d.RowID
  inner join LIBMstSubCategory s on  i.SubCategory = s.Id
  where (i.Scrap is null or i.Scrap=0) 
  and i.IsActive=1
  and i.KitId=0' + ' '  + @Query + ' ' +
  'and 0=0 order by i.ID'
 
PRINT @str
EXEC @str

end
go

当我执行上面的过程时，它已成功编译，但是当我使用此语句运行它时 EXEC LibFetchFilterItem'和i.Code =''10''' 然后



消息203，级别16，状态2，过程LibFetchFilterItem，行40

名称'

选择ID

，i.CodeVersion>的情况; 1然后i.Code +'+。+'+ convert（varchar，i.CodeVersion）否则i.Code结束为Code

，i.OldCode

，HRMS。 dbo.InitialCap（i。[Name]）as Name

，i。[Description]

，d.DeptName as Department

，HRMS .dbo.InitialCap（p。[Name]）as Product

，Edition

，[Version]

，PublisherBrand

，国家

，KeywordDesc

，l。[name] as Location

，HRMS.dbo.InitialCap（s。[Name ]）作为SubCategory

，剧情简介

，i.Website

来自LIBMstItem我内部加入LIBMstLocation l on i.LocationId = l.LocationId

内部联接LIBMstProduct p on i.ProductCategory = p.Id

左外连接HRMS.dbo。 Master_EmployeeMaster u on i.Brought'不是有效的标识符。



错误发布..但是当我打印查询时它是100％正确...

Plz帮帮我...

When i Execute the above procedure it's compiled successfully, but when i run it using this statement EXEC LibFetchFilterItem 'and i.Code=''10''' then

Msg 203, Level 16, State 2, Procedure LibFetchFilterItem, Line 40
The name '
select ID
,case when i.CodeVersion>1 then i.Code+'+.+'+convert(varchar,i.CodeVersion) else i.Code end as Code
,i.OldCode
,HRMS.dbo.InitialCap(i.[Name]) as Name
,i.[Description]
,d.DeptName as Department
,HRMS.dbo.InitialCap(p.[Name]) as Product
,Edition
,[Version]
,PublisherBrand
,Country
,KeywordDesc
,l.[name] as Location
,HRMS.dbo.InitialCap(s.[Name]) as SubCategory
,Synopsis
,i.Website
from LIBMstItem i inner join LIBMstLocation l on i.LocationId=l.LocationId
inner join LIBMstProduct p on i.ProductCategory = p.Id
Left outer join HRMS.dbo.Master_EmployeeMaster u on i.Brought' is not a valid identifier.

error Occured .. But when i just print the query it is 100% right ...
Plz help me ...

推荐答案

试试这个让我们知道

Try this and let us know

USE [ABC]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[LibFetchFilterItem]
@Query nvarchar(1000)
 
AS
Begin
set nocount on
 
DECLARE @str varchar(8000)
 
SET @str = N'
  select ID
  ,case  when i.CodeVersion>1 then i.Code+''+'''.'''+''+convert(varchar,i.CodeVersion) else i.Code end as Code
  ,i.OldCode
  ,HRMS.dbo.InitialCap(i.[Name]) as Name
  ,i.[Description]
  ,d.DeptName as Department
  ,HRMS.dbo.InitialCap(p.[Name]) as Product
  ,Edition
  ,[Version]
  ,PublisherBrand
  ,Country
  ,KeywordDesc
  ,l.[name] as Location
  ,HRMS.dbo.InitialCap(s.[Name]) as SubCategory
  ,Synopsis
  ,i.Website
  from LIBMstItem i inner join LIBMstLocation l  on i.LocationId=l.LocationId
  inner join LIBMstProduct p on i.ProductCategory = p.Id
  Left outer join  HRMS.dbo.Master_EmployeeMaster u on i.BroughtBy=u.[NewID]
  Left outer join  dynamicreport.dbo.CompanyMst c on i.CompanyId = c.CompanyId
  Left outer join  HRMS.dbo.Master_DepartmentMaster d on i.DepartmentID = d.RowID
  inner join LIBMstSubCategory s on  i.SubCategory = s.Id
  where (i.Scrap is null or i.Scrap=0) 
  and i.IsActive=1
  and i.KitId=0' + ' '  + @Query + ' ' +
  'and 0=0 order by i.ID'
 
PRINT @str
EXEC @str
 
end
go

我希望它能解决你的问题

I hope it will solve your problem

这篇关于exec失败，因为名称不是有效的标识符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！