cCourseID vCourseName sPeriod sCredit
000001 vs.net 20 6
000002 xml 6 2
000003 ado.net 13 3
000004 java 40 9
我的问题是:有如上课程表,我想取出上述课程名的ID号给变量,怎么写呀,我写的:
declare @vsid char(6)
declare @xmlid char(6)
declare @adoid char(6)
declare @javaid char(6)
select (case vCourseName when 'vs.net' then @vsid=cCourseID
when 'xml' then @xmlid=cCourseID
when 'ado.net' then @adoid=cCourseID
when 'java' then @javaid=cCourseID end) as a
from Courseselect @vsid,@xmlid,@adoid,@javaid若我没有讲明白,请明示,再线等
000001 vs.net 20 6
000002 xml 6 2
000003 ado.net 13 3
000004 java 40 9
我的问题是:有如上课程表,我想取出上述课程名的ID号给变量,怎么写呀,我写的:
declare @vsid char(6)
declare @xmlid char(6)
declare @adoid char(6)
declare @javaid char(6)
select (case vCourseName when 'vs.net' then @vsid=cCourseID
when 'xml' then @xmlid=cCourseID
when 'ado.net' then @adoid=cCourseID
when 'java' then @javaid=cCourseID end) as a
from Courseselect @vsid,@xmlid,@adoid,@javaid若我没有讲明白,请明示,再线等
set @course = 'vs.net'select @vsid = cCourseID from table where vCourseName = @course对于应用程序,一般做成存储过程,传入课程名,获得课程号.
--这样
create table Course
(
cCourseID nvarchar(10),
vCourseName nvarchar(10),
sPeriod int,
sCredit int
)
insert course select '000001', 'vs.net', 20, 6
insert course select '000002', 'xml', 6, 2
insert course select '000003', 'ado.net', 13, 3
insert course select '000004', 'java', 40, 9 declare @sql nvarchar(100)
set @sql=''
select @sql=@sql+cCourseID+'--'+vCourseName+','
from course
select @sql
set @course = 'vs.net'
select @vsid = cCourseID from table where vCourseName = @course 要注意的是
一次只能搞一个变量,取一个会值,
要取多个值,就要想其它方法了