！~~~~~~~~郁闷不知道错在那里！~~~~~~~~~~~

Question

我写了一个存储过程创建没有问题
运行就有问题是什么原因啊
create procedure checkuser
@usertype int,
@userinfo varchar(200),
@userpwd varchar(200)
as
begin
declare @strSql NVARCHAR(500)
if(@usertype>0)
set @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.username='+@userinfo+' and dbo.ayPassWord.ayPassWord='+@userpwd
else
set  @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.userEmail='+@userinfo+' and dbo.ayPassWord.ayPassWord='+@userpwdEXECUTE sp_executesql @strSql
print @usertype
print @strSql
end
exec checkuser 1,'ddd','123456'服务器: 消息 207，级别 16，状态 3，行 1
列名 'ddd' 无效。给点帮助啊

Answer 1

create procedure checkuser
@usertype int,
@userinfo varchar(200),
@userpwd varchar(200)
as
begin
declare @strSql NVARCHAR(500)
if(@usertype>0)
set @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.username='''+@userinfo+''' and dbo.ayPassWord.ayPassWord='''+@userpwd+''''
else
set  @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.userEmail='''+@userinfo+''' and dbo.ayPassWord.ayPassWord='''+@userpwd+''''EXECUTE sp_executesql @strSql
print @usertype
print @strSql
end

Answer 2

--try
create procedure checkuser
@usertype int,
@userinfo varchar(200),
@userpwd varchar(200)
as
begin
declare @strSql NVARCHAR(500)
if(@usertype>0)
set @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.username='''+@userinfo+''' and dbo.ayPassWord.ayPassWord='+@userpwd
else
set  @strSql='SELECT dbo.ayUser.UserID,dbo.ayUser.UserName,dbo.ayUser.UserEmail,dbo.ayUser.PassWordID, dbo.ayPassWord.ayPassWordID,dbo.ayPassWord.ayPassWord FROM dbo.ayPassWord INNER JOIN dbo.ayUser ON dbo.ayUser.PassWordID = dbo.ayPassWord.ayPassWordID where dbo.ayUser.userEmail='''+@userinfo+''' and dbo.ayPassWord.ayPassWord='+@userpwdEXECUTE sp_executesql @strSql
print @usertype
print @strSql
end

Answer 3

@userinfo , @userpwd  都是字段要比较的值，所以要加上引号，在表达式中 '' 代表 '
如果不加引号 ，它会认为是列，当列来处理，所以就会出现 列名 'ddd' 无效。
用上面的试试