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屋!