表:student(学生表)
有三列,分别为:S_NAME(学生姓名),S_AGE(学生年龄),S_SEX(学生性别);
需求:查出给定年龄和性别的所有学生姓名,并用“;”拼接返回。
函数代码:
CREATE FUNCTION GET_ALL_NAME_BY_PARA(@SEX VARCHAR(32),@AGE int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @NAME VARCHAR(10)
DECLARE @OUTP_ARA varchar(1000)
DECLARE GET_NAME CURSOR FOR SELECT S_NAME FROM student WHERE S_SEX=@SEX AND S_AGE=@AGE
OPEN GET_NAME
FETCH GET_NAME INTO @NAME
WHILE @@sqlstatus != 2
BEGIN
SET @outPara=@outPara || ';' || @NAME
FETCH GET_NAME INTO @NAME
END
CLOSE GET_NAME
DEALLOCATE GET_NAME
IF(DATALENGTH(@NAME)>0)
BEGIN