我在SQL Server2000中写SQL语句遇见的问题,请高手帮忙!!!!(100分)

T

tomol

Unregistered / Unconfirmed
GUEST, unregistred user!
我在SQL Server 2000的查询分析器里这样写:
declare @attrNo char(4), @attrValue char(20), @str char(100)
set @attrNo='0001'
set @attrValue='柔軟PVC'
set @str='Select * from TmcMaterial where attrNo='+@attrNo+
' and attrValue='+@attrValue
exec(@str)
结果报错说 "列名 '柔軟PVC' 无效。"
我用print @str察看SQL语句为
Select * from TmcMaterial where attrNo=0001 and attrValue=柔軟PVC
请问,为什么没有给0001和柔軟PVC加上引号,如何给它们加上引号???
这种情况怎么办?请高手指点!!!
急,多谢多谢
 
set @str='Select * from TmcMaterial where attrNo='+ ''''+
@attrNo+''''+ and attrValue='+ ''''+ @attrValue +''''
 
to youou:
不是在Delphi里,是在SQL Server里
 
declare @attrNo char(4), @attrValue char(20), @str char(100)
set @attrNo='0001'
set @attrValue='柔軟PVC'
set @str='Select * from TmcMaterial where attrNo='''+@attrNo+
''' and attrValue='''+@attrValue +'''';
print @str
SQLSERVER通过.结果
Select * from TmcMaterial where attrNo='0001' and attrValue='柔軟PVC '
 
就是啊。。也是这样子的~不过楼上那也是一样的。
 
同意楼上的。
主要原因是字符串的处理错误
 
set @str='Select * from TmcMaterial where attrNo='+#39+@attrNo+#39+
' and attrValue='++#39+@attrValue+#39
 
你再看一下吧,我认为你用char类型不太好,因它要补充空格,容易出问题。
以下是我的改正结果,你看一下有没有用:

declare @attrNo char(4), @attrValue char(20), @str char(100)
set @attrNo='0001'
set @attrValue='柔軟PVC'
set @str='Select * from TmcMaterial where attrNo='''+@attrNo+
''' and attrValue = '''+@attrValue + ''''

select @str

--结果:
---------------------------------------------------------
Select * from TmcMaterial where attrNo='0001' and attrValue = '柔軟PVC '

(所影响的行数为 1 行)

--改用varchar:
declare @attrNo varchar(4), @attrValue varchar(20), @str varchar(100)
set @attrNo='0001'
set @attrValue='柔軟PVC'
set @str='Select * from TmcMaterial where attrNo='''+@attrNo+
''' and attrValue = '''+@attrValue + ''''

select @str

--结果:
----------------------------------------------------------
Select * from TmcMaterial where attrNo='0001' and attrValue = '柔軟PVC'

(所影响的行数为 1 行)

语句绝对没错,但不知你的数据表是怎么样的, 会不会出错。
 
我的存储过程如下:
在ADO两层下正常,但在三层下(用socketconnection)出错,请大侠们指点
CREATE PROCEDURE SPmcFilterMat
@inNo char(4),
@attrNoStr char(100),
@attrValueStr nvarchar(250)
AS
--analyze attrNoStr, attrValueStr
declare @attrNo char(4),
@attrValue nvarchar(60),
@sInitSQL char(60),
@sOtherSQL nvarchar(600),
@iPos int,
@iPos2 int,
@iFirst int,
@sTemp nvarchar(1)
------------------------------------------------
/*Select matNo
from VmcMatMainAttrs
where inNo=@inNo
and attrNo=@attrNo
and attrValue=@attrValue
return
*/
-------------------------------------------------
Set @sTemp = '~'
Select @iPos=CharIndex(@sTemp, @attrNoStr)
if @iPos=0
begin
if ltrim(rtrim(@attrNoStr)) = ''
return
Select @attrNo = @attrNoStr
Select @attrValue = @attrValueStr
Select matNo, matName
from VmatName
where matNo in
(Select matNo
from VmcMatMainAttrs
where inNo = @inNo
and attrNo = @attrNo
and attrValue = @attrValue
)
return
end
else
begin
Select @iFirst = 1
while @iPos>0
begin
Select @attrNo = SubString(@attrNoStr, 1, @iPos-1)
Select @iPos2 = CharIndex(@sTemp, @attrValueStr)
Select @attrValue = SubString(@attrValueStr, 1, @iPos2-1) --这一句出错
Select @sInitSQL = ' Select matNo from VmcMatMainAttrs where inNo='+@inNo
if @iFirst=1
begin
Select @sOtherSQL = ' Select matNo from VmcMatMainAttrs where attrNo = ' + @attrNo +
' and attrValue = ''' +ltrim(rtrim(@attrValue)) + ''' and matNo in (' + @sInitSQL + ') '
Select @iFirst = 2
end
else
begin
Select @sOtherSQL = ' Select matNo from VmcMatMainAttrs where attrNo = ' + @attrNo +
' and attrValue = ''' + ltrim(rtrim(@attrValue)) + ''' and matNo in (' + rtrim(@sOtherSQL) + ') '
end
if @iPos=Len(@attrNoStr)
begin
Exec(@sOtherSQL)
return
end
else
begin
Select @attrNoStr = SubString(@attrNoStr, @iPos+1, Len(@attrNoStr)-@iPos)
Select @attrValueStr = SubString(@attrValueStr, @iPos2+1, Len(@attrValueStr)-@iPos2)
Select @iPos = CharIndex(@sTemp, @attrNoStr)
end
end
end
GO
 
什么错误
 
顶部