请问这两条查询语句，能不能通过拼接写成一条SQL语句？
比如，有两张表student（班级编号cid，姓名sname，学生sid）和grade_sid（sid,成绩gpoind),一个学生id就对应一张表,比如学生编号是100，学生成绩表就是'grade_100'，我现在传一个学生的姓名'xiaoming'过来，求整个班学生的成绩；
我的做法 select sid from student where cid=(select cid from student where sname='xiaoming')
然后遍历上面,把所有的sid，都组装grad_sid表，同一个班有多个同学就有多张表；
最后我循环使用 select * from grad_sid 查找学生成绩

请问有没有通过一条sql语句实现，我现在是两条，因为c#不支持多个连接，所以每次都查完断开连接又重新找
[解决办法]
CREATE PROC [dbo].[usp_GetClassCredts](
@ClassName varchar(20),
@StudentName varchar(20)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @cid int;

IF(@ClassName is NOT NULL)
BEGIN
SELECT @cid = cid FROM Class WHERE ClassName = @ClassName;
END
ELSE IF (@StudentName IS NOT NULL)
BEGIN
SELECT @cid = cid FROM student WHERE sname = @StudentName;
END
ELSE
BEGIN
PRINT 'Please give valid Class or Student name.';
Return;
END

IF @cid IS NOT NULL
BEGIN
SELECT *
FROM grade_100
WHERE cid = @cid;
END
ELSE
BEGIN
PRINT 'The Class, ' + @ClassName + ' or ' + @StudentName + ', does not exits.';
Return;
END
GO
[解决办法]

USE test
GO


-->生成表student

if object_id('student') is not null 
drop table student
Go
Create table student([cid] smallint,[sname] nvarchar(2),[sid] nvarchar(3))
Insert into student
Select 1,N'三',N'001'
Union all Select 1,N'李四',N'002'
Union all Select 1,N'王五',N'003'

-->生成表grade_001

if object_id('grade_001') is not null 
drop table grade_001
Go
Create table grade_001([sid] nvarchar(3),[gpoind] smallint)
Insert into grade_001
Select N'001',93

-->生成表grade_002

if object_id('grade_002') is not null 
drop table grade_002
Go
Create table grade_002([sid] nvarchar(3),[gpoind] smallint)
Insert into grade_002
Select N'002',81

-->生成表grade_003

if object_id('grade_003') is not null 
drop table grade_003
Go
Create table grade_003([sid] nvarchar(3),[gpoind] smallint) 
 
Insert into grade_003
Select N'003',98

Go





DECLARE @sname NVARCHAR(50),@sql NVARCHAR(MAX)

SET @sname='三'



SELECT @sql=ISNULL(@sql+' Union all ','')+'Select * from grade_'+LTRIM(sid) FROM student AS a 
WHERE EXISTS(SELECT 1 FROM student AS x 
WHERE x.cid=a.cid
AND x.sname=@sname
)

Exec  ('Select * from ('+@sql+') As t')

/*
print :

Select 
* 
from (
Select * from grade_001 Union all 
Select * from grade_002 Union all 
Select * from grade_003
) As t

Result:
sid  gpoind
---- ------
001  93
002  81
003  98
*/