从存储过程返回字符串 [英] return string from stored procedure

问题描述

大家好，



我是Ado.net的新手。



我写的sotred过程和来自sp的返回字符串。命令成功完成但是当我执行错误时来了



我的代码是

  USE  [测试] 
  GO  
  / *   *****对象：StoredProcedure [dbo]。[GetSamples]脚本日期：04/02/2013 14:29:18 ** **** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROC  [dbo]。[GetSamples]（ @Id   INT ）
  AS  
  BEGIN  
  DECLARE   @ Message   VARCHAR （ 250 ）
 
  IF   EXISTS （ SELECT  * 
  FROM 示例
  WHERE  id =  @ Id ）
  BEGIN  
  SELECT  * 
  FROM 示例
  WHERE  id =  @ Id  
 
  SET  @ Message = ' 记录已存在' 
 
  SELECT   @ Message  
  return   @Message  
  END  
  ELSE  
  BEGIN  
  SET  @ Message = ' 记录不存在' 
 
  SELECT   @ Message  
  return   @ Message  
 < span class =code-keyword> END  
  END  

执行错误时

（ 1 行受影响）
 Msg  245 ，等级 16 ，State  1 ， Procedure  GetSamples，Line  25  
转换失败 转换 varchar 值' 记录不存在'  数据类型  int 。

给我打电话给我出错了。



提前谢谢......

解决方案

两种方式......

1.你可以使用输出参数

2.使用执行标量。



你的sp应该是这样的下面（第二个选项）

  USE  [测试] 
  GO  
  / *   *****对象：StoredProc edure [dbo]。[GetSamples]脚本日期：04/02/2013 14:29:18 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROC  [dbo]。[GetSamples]（ @ Id   INT ）
  AS  
  BEGIN  
  DECLARE   @ Message   VARCHAR （ 250  ）
 
  IF   EXISTS （ SELECT  * 
  FROM 示例
  WHERE  id =  @ Id ）
  BEGIN  
  SELECT  * 
  FROM 示例
  WHERE  id =  @ Id  
 
  SET  @ Message = '  记录已存在' 
 
  SELECT   @ Message  
  return    -    ***************更改 
  END  
 < span class =code-keyword> ELSE  
  BEGIN  
  SET  @ Message = ' 记录不存在' 
 
  SELECT  < span class =code-sdkkeyword> @ Message  
  return    -    ***************更改 
  END  
  END  

这样播放........... ............



ALTER程序[dbo]。[InsUpdGuest_Info]

@guest_id numeric（18 ，0）=空，

@membership_id数字（18,0）= null，

@guest_name nvarchar（100），

@ contact1 nvarchar（20）= null，

@ contact2 nvarchar（20）= null，

@landline nvarchar（20）= null，

@ email nvarchar（50）= null，

@address nvarchar（500）= null，

@date_of_birth datetime = null，

@spouse_dob datetime = null，

@wed_aniversary datetime = null ，

@reffered_by numeric（18,0）= Null，

@IsActive bit = null，

@user_id numeric（18,0） = null，

@RetVal As nvarchar（20）=空输出

As

开始

如果不存在（选择*来自tblGuestInfo其中guest_id<> @guest_id和guest_name = @guest_name）

开始

如果不存在（选择*来自tblGuestInfo，其中guest_id = @ guest_id）

开始

开始尝试

begin tran;

DECLARE @InsertedRows TABLE（ID numeric（18,0））

插入到tblGuestInfo（membership_id

，guest_name

，contact1

，contact2

，固定电话

，电子邮件

，地址

，date_of_birth

，spouse_dob

，wed_aniversary

，reffered_by

，IsActive

，创建

，created_by）

/ *获取输出ID * /

OUTPUT inserted.guest_id进入@InsertedRows

/ * ------ --------- * /

值（@membership_id

，@ guest_name

，@ contact1

，@ contact2

，@ landline

，@ email

，@ address

，@ date_of_birth

，@ spouse_dob

，@ wed_aniversary

，@ reffered_by

，@ IsActive

，getdate（）

，@ user_id）;

选择@RetVal =转换（nvarchar，ID）来自@InsertedRows

commit;

返回1;

结束尝试

开始捕获

回滚;

返回0;

结束捕获

结束

否则

开始

开始尝试

begin tran;

更新tblGuestInfo设置

membership_id = @membership_id，

guest_name = @guest_name，

contact1 = @ contact1，

contact2 = @ contact2，

landline = @landline，

email = @email，

地址= @地址，

date_of_birth = @date_of_birth，

spouse_dob = @spouse_dob ，

wed_aniversary = @wed_aniversary，

reffered_by = @reffered_by，

IsActive = @IsActive，

updated = getdate（），

updated_by = @user_id

其中guest_id = @guest_id;



从tblGuest_Family_Info删除guest_id = @guest_id;

从tblGuest_Account删除其中guest_id = @reffered_by d trans_id = @ guest_id和trans_type =''REF'';



设置@RetVal =转换（nvarchar，@ guest_id）;

提交;

结束尝试

开始捕获

回滚;

设置@RetVal =''False'';

结束捕获

结束

结束

否则

开始

设置@RetVal =''已经存在'';

结束

结束

如果你想要返回状态消息......像这样改变您的SP

  USE  [测试] 
  GO  
  / *   *****对象：StoredProcedure [dbo]。[GetSamples]脚本日期：04/02/2013 14:29:18 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER  < span class =code-keyword> PROC  [dbo]。[GetSamples]（ @ Id   INT ）
  AS  
  BEGIN  
  DECLARE   @ Message   VARCHAR （ 250 ）
 
  IF   EXISTS （ SELECT  * 
  FROM 示例
  WHERE  id =  @ Id ）
  BEGIN  
 
  SET  @ Message = ' < span class =code-string>记录已存在' 
 
  SELECT   @Message  
  END  
  ELSE  
  BEGIN  
  SET  @ Message = ' 记录不存在' 
 
  SELECT   @Message  
  END  
  END  

Hi all,

I am new of the Ado.net.

I am writing sotred procedure and return string from sp. command successfully complete but when I execute error is came

my code is

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)

      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id

            SET @Message='record already exist'

            SELECT @Message
            return @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'

            SELECT @Message
            return @Message
        END
  END

when execute error came

(1 row(s) affected)
Msg 245, Level 16, State 1, Procedure GetSamples, Line 25
Conversion failed when converting the varchar value 'record does not exist' to data type int.

tel me where I went wrong.

Thanks in advance......

解决方案

two ways...
1. you can use output parameter
2. use execute scalar.

your sp should be like below(for second option)

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id
 
            SET @Message='record already exist'
 
            SELECT @Message
            return -- ***************change
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
            return -- ***************change
        END
  END

Play like this.......................

ALTER Procedure [dbo].[InsUpdGuest_Info]
@guest_id numeric(18, 0)=Null,
@membership_id numeric(18, 0)=null,
@guest_name nvarchar(100),
@contact1 nvarchar(20)=null,
@contact2 nvarchar(20)=null,
@landline nvarchar(20)=null,
@email nvarchar(50)=null,
@address nvarchar(500)=null,
@date_of_birth datetime=null,
@spouse_dob datetime=null,
@wed_aniversary datetime=null,
@reffered_by numeric(18, 0)=Null,
@IsActive bit = null,
@user_id numeric(18, 0)=null,
@RetVal As nvarchar(20)= Null output
As
Begin
If Not Exists(Select * From tblGuestInfo Where guest_id<>@guest_id And guest_name = @guest_name)
Begin
if Not Exists(Select * From tblGuestInfo Where guest_id=@guest_id)
Begin
Begin Try
begin tran;
DECLARE @InsertedRows TABLE (ID numeric(18,0))
Insert Into tblGuestInfo(membership_id
,guest_name
,contact1
,contact2
,landline
,email
,address
,date_of_birth
,spouse_dob
,wed_aniversary
,reffered_by
,IsActive
,created
,created_by)
/* To get output ID */
OUTPUT inserted.guest_id Into @InsertedRows
/*---------------*/
Values(@membership_id
,@guest_name
,@contact1
,@contact2
,@landline
,@email
,@address
,@date_of_birth
,@spouse_dob
,@wed_aniversary
,@reffered_by
,@IsActive
,getdate()
,@user_id);
Select @RetVal = Convert(nvarchar,ID) From @InsertedRows
commit;
return 1;
End Try
Begin Catch
rollback;
return 0;
End Catch
End
Else
Begin
Begin try
begin tran;
Update tblGuestInfo Set
membership_id = @membership_id,
guest_name = @guest_name,
contact1 = @contact1,
contact2 = @contact2,
landline = @landline,
email = @email,
address = @address,
date_of_birth = @date_of_birth,
spouse_dob = @spouse_dob,
wed_aniversary = @wed_aniversary,
reffered_by = @reffered_by,
IsActive = @IsActive,
updated = getdate(),
updated_by = @user_id
Where guest_id = @guest_id;

Delete From tblGuest_Family_Info Where guest_id = @guest_id;
Delete From tblGuest_Account Where guest_id = @reffered_by And trans_id=@guest_id And trans_type=''REF'';

Set @RetVal = Convert(nvarchar,@guest_id);
commit;
End try
Begin Catch
rollback;
Set @RetVal = ''False'';
End Catch
End
End
Else
Begin
Set @RetVal = ''Already Existing'';
End
End

If you want to just return the status message...alter your SP like this

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            
            SET @Message='record already exist'
 
            SELECT @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
        END
  END

这篇关于从存储过程返回字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！