exec执行的问题，急

[Bmonkey]

CREATE PROCEDURE test
@Condition varchar(100)
AS
declare @sql nvarchar(400)
set @sql='select * from student where Sdept='+@Condition
print(@sql)
exec(@sql)
GO


如上存储过程，执行的时候有错误，用print打出来是例如
select * from student where Sdept=MA
这样的，我想的得到
select * from student where Sdept=‘MA’
这样的@sql串该怎么改啊，谢谢

[lsxaa]

CREATE PROCEDURE test
@Condition varchar(100)
AS
declare @sql nvarchar(400)
set @sql='select * from student where Sdept='''+@Condition+''''
print(@sql)
exec(@sql)
GO

[xluzhong]

sql中一律用两个单引号表示单引号

CREATE PROCEDURE test
@Condition varchar(100)
AS
declare @sql nvarchar(400)
set @sql='select * from student where Sdept='''+@Condition+''''
print(@sql)
exec(@sql)
GO

[Bmonkey]

CREATE PROCEDURE N_GetRecordsByCondition
@SearchType nvarchar(50),
@Condition nvarchar(100)
AS
declare @ExecuteSql nvarchar(400)
set @ExecuteSql = N'select S.Sno,S.Sname,S.Ssex,S.Sage,S.Sdept,C.Cname,SC.Grade from Student as S left join SC on S.Sno=SC.Sno left join Course as C on SC.Cno=C.Cno'
set @ExecuteSql = @ExecuteSql + ' where @SearchType = ''' +@Condition+''''
print(@ExecuteSql)
exec sp_executesql @ExecuteSql,N'@SearchType nvarchar(50)',@SearchType
GO


如上存储过程，其中
select S.Sno,S.Sname,S.Ssex,S.Sage,S.Sdept,C.Cname,SC.Grade from Student as S left join SC on S.Sno=SC.Sno left join Course as C on SC.Cno=C.Cno
这句可以取出的值有
（部分）
Sno Sname
----- --------------------
95001 李涌
95001 李涌
95001 李涌
95002 刘晨
95002 刘晨
95002 刘晨
95003 王敏
95004 张立


最后，我这样执行存储过程，怎么不能取出值呢？
declare @SearchType varchar(50)
declare @Condition varchar(100)
set @SearchType='S.Sname'
set @Condition='李涌'
exec N_GetRecordsByCondition @SearchType,@Condition

[xluzhong]

看你调用存储过程应该是这样，而不是用sp_executesql

CREATE PROCEDURE N_GetRecordsByCondition
@SearchType nvarchar(50),
@Condition nvarchar(100)
AS
declare @ExecuteSql nvarchar(400)
set @ExecuteSql = N'select S.Sno,S.Sname,S.Ssex,S.Sage,S.Sdept,C.Cname,SC.Grade from Student as S left join SC on S.Sno=SC.Sno left join Course as C on SC.Cno=C.Cno'
set @ExecuteSql = @ExecuteSql + ' where '+@SearchType+' = ''' +@Condition+''''
print(@ExecuteSql)
exec (@ExecuteSql)
GO

[Bmonkey]

我昨天那样写的时候都调用成功了，后来改了改就出错了，这样可以用就先这样用，过后再来想了