我试过SQL查询显示错误

本文介绍了我试过SQL查询显示错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ SELECT A.farmer_id，B.firstname + ' ' ' ' + B.lastname + ' ' ' ' + B.surname as farmer_name，A.farmer_season，B.gender，' ' - ' ' as farmerphoto，B.dateofbirth，B.birthlocation，B.fathername，B.mothername， B.phonenumber，' ''' as 观察，B.nooffarms，B.produtionestimation ，B.regionname，B.districtname as districtname，B.subd istrictname，B.villagename，B.sectionname，B.zonename， B.lastyearcooperative，B.currentyearcooperative as currentyearcoop，A.farm_no， J.name as fieldstaffname， A.farm_visitno，[OLAM DEV1 OLAMDEV1 OLAMDEV1]，[Phonenumber] 来自（选择 farmer_name，A.farm_detailsdata FROM dbo.tbl_farmer_farmdetails_ ' + @originname +' A） as a PIVOT（MAX（A.farm_detailsdata） for farmer_name in （[OLAM DEV1 OLAMDEV1 OLAMDEV1]， [Phonenumber]）） as b INNER JOIN dbo.tb l_farmerregistration_ ' + @originname +' B ON B.farmerctscode = A.farmer_id INNER JOIN dbo.tbl_villagemaster_ ' + @originname +' C ON C.villageid = B .village INNER JOIN dbo.tbl_registration J ON J.regid = A.createdby WHERE B.farmerseason = ' 2018' 当我执行上述查询时，显示错误如下 'A'附近的语法不正确关键字'as'附近的语法不正确 上面代码中的错误是什么 我尝试过： SELECT A.farmer_id，B.firstname + ' ' ' ' + B.lastname + ' ' ' ' + B.surname as farmer_name，A.farmer_season，B.gender，' ' - ' ' as farmerphoto，B .dateofbirth，B.birthlocation，B.fathername，B.mothername， B.phonenumber，' '' ' as 观察，B.nooffarms，B.produtionestimation，B.regionname，B.districtname as districtname，B.subdistrictname，B.villagename，B.sectionname，B.zonename， B.lastyearcooperative，B.currentyearcooperative as currentyearcoop，A.farm_no， J.name as fieldstaffname， A.farm_visitno，[OLAM DEV1 OLAMDEV1 OLAMDEV1]，[Phonenumber] 来自（选择 farmer_name，A.farm_detailsdata FROM dbo.tbl_farmer_farmdetails_ ' + @originname +' A） as a PIVOT（MAX（A.farm_detailsdata） for farmer_name （[OLAM DEV1 OLAMDEV1 OLAMDEV1]， [Phonenumber]）） b INNER JOIN dbo.tbl_farmerregistration_ ' + @originname + ' B ON B.farmerctscode = A.farmer_id INNER JOIN dbo.tbl_villagemaster_ ' + @originname +' C ON C.villageid = B.village INNER JOIN dbo.tbl_registration J ON J.regid = A.createdby WHERE B.farmerseason = ' 2018' 当我执行上述查询时，显示错误如下 'A'附近的语法不正确关键字'as'附近的语法错误 什么是我上面代码中的错误解决方案 这个： FROM dbo .tbl_farmer_farmdetails_ ' + @originname +' A） as a 我完全不确定你要在那里做什么，但如果你试图将部分表名作为参数传递，那么： 1）这不是这样做的方式。 和 2）这是一个非常糟糕的主意。永远不要将参数连接到SQL命令 - 它会让您对SQL注入完全开放，这可能会损坏或破坏您的数据库。 即使您添加了引号以使其编译作为一个命令，它仍然无法工作 - 你不能构建一个表名而不构造一个字符串，这是一个代替其参数的整个命令，并使用EXEC来运行它。 我认为你的数据库设计需要一些工作：你必须通过串联来选择多个表，这不是一个好主意！ 如果你想构建像那样的表名你需要使用动态SQL - 参见 在SQL Server中执行动态SQL命令 [ ^ ] 这是一个简单示例 create table ＃table1（i int ，b int ） insert 进入＃table1 值（ 1 ， 2 ） 选择 * 来自 ＃table1 - WORKS 声明 @ o varchar （ 10 ）= ' 1' 选择 * [来自 ' ＃table' + @ o ] - 不工作......语法不正确靠近'from'#table'+ @o'。 你需要先创建你的SQL命令，然后执行它 eg 声明 @sql nvarchar （max）= ' select * from #table' + @ o EXEC sp_executesql @sql 我真的不明白你为什么要参数化表名 - 不太可能真的需要这样做。 如果要使用参数化表名，应该尽量避免SQL注入攻击的风险。一种技术是将有效的表名放入一个表中，使用传递的参数来查找，例如 create table #validnames（originname varchar （ 30 ），fulltablename varchar （ 255 ）） insert into #validnames（originname，fulltablename） values （' 1'， ＃table1'），（' 2'，' ＃table2'）然后就可以像这样使用 声明 @ tn varchar （ 255 ）=（ SELECT fulltablename 来自 #validnames 其中​​ originname = @ o ） IF @ tn IS NULL RAISERROR （ 15600 ， - 1，-1，' myCode'）; - 并退出子程序 ELSE SELECT @ tn - 在动态sql中使用此值 如果有人恶意将某些SQL作为参数的一部分传递，那么它将不会在表中，返回的tablename将为null，并且将引发错误，从而保护您的数据库。 SELECT A.farmer_id,B.firstname + '' '' + B.lastname + '' '' + B.surname as farmer_name,A.farmer_season,B.gender,''-'' as farmerphoto,B.dateofbirth,B.birthlocation,B.fathername,B.mothername, B.phonenumber,'''' as observation,B.nooffarms,B.produtionestimation,B.regionname,B.districtname as districtname,B.subdistrictname,B.villagename,B.sectionname,B.zonename,B.lastyearcooperative,B.currentyearcooperative as currentyearcoop,A.farm_no,J.name as fieldstaffname, A.farm_visitno,[OLAM DEV1 OLAMDEV1 OLAMDEV1],[Phonenumber] from (select farmer_name,A.farm_detailsdata FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A ) as a PIVOT (MAX(A.farm_detailsdata) for farmer_name in ([OLAM DEV1 OLAMDEV1 OLAMDEV1], [Phonenumber])) as b INNER JOIN dbo.tbl_farmerregistration_' + @originname + ' B ON B.farmerctscode = A.farmer_id INNER JOIN dbo.tbl_villagemaster_' + @originname + ' C ON C.villageid = B.villageINNER JOIN dbo.tbl_registration J ON J.regid = A.createdby WHERE B.farmerseason = '2018' When i execute the above query shows error as follows Incorrect syntax near 'A'Incorrect syntax near the keyword 'as' what is the mistake in my above codeWhat I have tried:SELECT A.farmer_id,B.firstname + '' '' + B.lastname + '' '' + B.surname as farmer_name,A.farmer_season,B.gender,''-'' as farmerphoto,B.dateofbirth,B.birthlocation,B.fathername,B.mothername, B.phonenumber,'''' as observation,B.nooffarms,B.produtionestimation,B.regionname,B.districtname as districtname,B.subdistrictname,B.villagename,B.sectionname,B.zonename,B.lastyearcooperative,B.currentyearcooperative as currentyearcoop,A.farm_no,J.name as fieldstaffname, A.farm_visitno,[OLAM DEV1 OLAMDEV1 OLAMDEV1],[Phonenumber] from (select farmer_name,A.farm_detailsdata FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A ) as a PIVOT (MAX(A.farm_detailsdata) for farmer_name in ([OLAM DEV1 OLAMDEV1 OLAMDEV1], [Phonenumber])) as b INNER JOIN dbo.tbl_farmerregistration_' + @originname + ' B ON B.farmerctscode = A.farmer_id INNER JOIN dbo.tbl_villagemaster_' + @originname + ' C ON C.villageid = B.villageINNER JOIN dbo.tbl_registration J ON J.regid = A.createdby WHERE B.farmerseason = '2018' When i execute the above query shows error as follows Incorrect syntax near 'A'Incorrect syntax near the keyword 'as' what is the mistake in my above code 解决方案 This: FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A ) as a I'm not at all sure what you are trying to do there, but if you are trying to pass part of the table name as a parameter then:1) That's not the way to do it.And2) That's a very bad idea. Never concatenate parameters into an SQL command - it leaves you wide open to SQL Injection which can damage or destroy your database.Even if you added the quotes to make it compile as a command, it still wouldn't work - you can't "build" a table name without constructing a string which is the entire command with its parameters substituted and using EXEC to run it.I think your database design needs some work: it's not a good idea to have multiple tables that you have to select by concatenation!If you want to construct the table name like that then you need to use Dynamic SQL - seeExecute Dynamic SQL commands in SQL Server[^]Here is a simple example create table #table1 (i int, b int)insert into #table1 values (1,2)select * from #table1--WORKSdeclare @o varchar(10) = '1'select * [from '#table' + @o]-- DOES NOT WORK ... "Incorrect syntax near 'from '#table' + @o'."You need to first create your SQL command and then execute ite.g. declare @sql nvarchar(max) = 'select * from #table' + @oEXEC sp_executesql @sqlI really don't understand why you would want to parameterise the table name - there is unlikely to be a real need to do this.You should try to avoid the risk of SQL Injection attack if you are going to use the parameterised table names. One technique is to put the valid table names into a table that you use the passed parameter to look up into e.g.create table #validnames(originname varchar(30), fulltablename varchar(255))insert into #validnames (originname, fulltablename) values('1', '#table1'),('2', '#table2')Then you can use it like thisdeclare @tn varchar(255) = (SELECT fulltablename from #validnames where originname = @o)IF @tn IS NULLRAISERROR (15600,-1,-1, 'myCode'); -- and exit your subroutineELSESELECT @tn -- use this value in your dynamic sqlIf someone has maliciously passed some SQL as part of the parameter then it won't be in the table, the tablename returned will null and an error will be raised, protecting your database. 这篇关于我试过SQL查询显示错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！