将varchar值'my my value'转换为数据类型int时转换失败

I get this error in this simple SQL statement when trying to retrieve a string from a table.

尝试从表中检索字符串时,我在这个简单的SQL语句中收到此错误。

Msg 245, Level 16, State 1, Procedure prViewRequirements, Line 18 Conversion failed when converting the varchar value 'Cardiac Assessment Questionnaire by Dr.' to data type int.

Msg 245,Level 16,State 1,Procedure prViewRequirements,Line 18转换varchar值'博士的心脏评估问卷'时转换失败到数据类型int。

/****** Object:  StoredProcedure [dbo].[prViewRequirements]   Script Date: 04/24/2013 15:44:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prViewRequirements]
    @WFRouteID int

AS

DECLARE 
@DocumentDescription VARCHAR(100)

SELECT @DocumentDescription = DocumentDescription
            FROM tbFollowOnTracking
            WHERE WFRouteID = @WFRouteID
            AND IsActive = 1


            IF (@@ERROR <> 0)
                GOTO ERRSP      

        RETURN @DocumentDescription

ERRSP:
    RETURN -1

Does anyone know why?

有谁知道为什么?

3 个解决方案

#1

You are trying to return a varchar instead of int.

您正在尝试返回varchar而不是int。

RETURN @DocumentDescription

Please either do

请做

select @DocumentDescription

or use an output parameter (Recommended)

或使用输出参数(推荐)

ALTER PROCEDURE [dbo].[prViewRequirements]
    @WFRouteID int
    , @DocumentDescription varchar(100) = null output

UPDATE - Here is the whole procedure:

更新 - 这是整个过程:

alter procedure dbo.prViewRequirements
    @WFRouteID int
    , @DocumentDescription varchar(100) = null output
AS

select @DocumentDescription = '' -- Init

select @DocumentDescription = DocumentDescription
from tbFollowOnTracking
where WFRouteID = @WFRouteID
and IsActive = 1

return 0
go

/* Examples
declare @DocumentDescription varchar(100) = ''

exec dbo.prViewRequirements @WFRouteID = 10, @DocumentDescription  = @DocumentDescription output

select @DocumentDescription 
*/

#2

Try this one -

试试这个 -

ALTER PROCEDURE [dbo].[prViewRequirements]

      @WFRouteID INT
    , @DocumentDescription VARCHAR(100) OUTPUT

AS BEGIN

    SELECT @DocumentDescription = t.DocumentDescription
    FROM dbo.tbFollowOnTracking t
    WHERE t.WFRouteID = @WFRouteID
        AND t.IsActive = 1

    IF @DocumentDescription IS NULL
        RETURN -1    

    RETURN 0

END

#3

Try This:

alter procedure dbo.prViewRequirements
    @WFRouteID int
    , @DocumentDescription varchar(100) = null output
AS

BEGIN
select @DocumentDescription = '' -- Init

select @DocumentDescription = DocumentDescription
from tbFollowOnTracking
where WFRouteID = @WFRouteID
and IsActive = 1

END

Execute the proc as shown below

执行proc,如下所示

DECLARE @res varchar(100)
exec dbo.prViewRequirements @WFRouteID,@DocumentDescription=@res OUTPUT
select @res

#1