在存储过程中定义一个变量通过查询语句却无法赋值,该怎么解决呢?
DECLARE @SchoolworkID INT
  SET @SchoolworkID = 0----可以获取值
  SELECT @SchoolworkID = (SELECT TOP 1 SchoolworkID FROM StudentSchoolwork WHERE(schoolworkPath = @schoolworkPath))------具体情况如下
CREATE PROCEDURE spUploadSchoolwork
  @studentNO CHAR(6),
  @schoolworkType CHAR(1),
  @schoolworkPath VARCHAR(200),
  ---@teacherMarkedSign CHAR(1),
  @schoolworkFileLength INT,
  ----uploadDay SMALLDATETIME DEFAULT GETDATE(),
  @schoolworkFileType CHAR(5)
AS
  DECLARE @SchoolworkID INT
  SET @SchoolworkID = 0
  SELECT @SchoolworkID = (SELECT TOP 1 SchoolworkID FROM StudentSchoolwork WHERE(schoolworkPath = @schoolworkPath))------这里@SchoolworkID无法获取值,该怎么解决呢?
-----PRINT @SchoolworkID-------
  IF (@SchoolworkID = 0)
  BEGIN
    INSERT INTO StudentSchoolwork
    VALUES(@studentNO, @schoolworkType, @schoolworkPath, '0', @schoolworkFileLength, GETDATE(), @schoolworkFileType)
  END
  ELSE
  BEGIN
    UPDATE StudentSchoolwork
    SET studentNO = @studentNO, 
        schoolworkFileLength = @schoolworkFileLength, 
        uploadDay = GETDATE()
    WHERE SchoolworkID = @SchoolworkID
  END
GO

解决方案 »

  1.   

    --这样呢?
    CREATE PROCEDURE spUploadSchoolwork
      @studentNO CHAR(6),
      @schoolworkType CHAR(1),
      @schoolworkPath VARCHAR(200),
      ---@teacherMarkedSign CHAR(1),
      @schoolworkFileLength INT,
      ----uploadDay SMALLDATETIME DEFAULT GETDATE(),
      @schoolworkFileType CHAR(5)
    AS
      DECLARE @SchoolworkID INT
      SET @SchoolworkID = 0
      SELECT TOP 1 @SchoolworkID=SchoolworkID FROM StudentSchoolwork WHERE(schoolworkPath = @schoolworkPath)------这里@SchoolworkID无法获取值,该怎么解决呢?
    -----PRINT @SchoolworkID-------
      IF (@SchoolworkID = 0)
      BEGIN
        INSERT INTO StudentSchoolwork
        VALUES(@studentNO, @schoolworkType, @schoolworkPath, '0', @schoolworkFileLength, GETDATE(), @schoolworkFileType)
      END
      ELSE
      BEGIN
        UPDATE StudentSchoolwork
        SET studentNO = @studentNO, 
            schoolworkFileLength = @schoolworkFileLength, 
            uploadDay = GETDATE()
        WHERE SchoolworkID = @SchoolworkID
      END
    GO
      

  2.   

    SELECT @SchoolworkID = (SELECT TOP 1 SchoolworkID FROM StudentSchoolwork WHERE(schoolworkPath = @schoolworkPath))------这里@SchoolworkID无法获取值,该怎么解决呢?
    -----------------------------------------------------------------------------------
    直接写不就得啦
    SELECT @SchoolworkID =SchoolworkID FROM StudentSchoolwork WHERE schoolworkPath = @schoolworkPath
      

  3.   

    当然这里得到的@SchoolworkID是满足
    WHERE schoolworkPath = @schoolworkPath条件
    的最后一条记录SchoolworkID的值