关于sql 2008 存储过程 返回值问题

[李哈哈]

创建一个存储
--up_gznh_get_body_s04 '','','1',''
alter procedure up_gznh_get_body_s04
@zyh varchar(20) ,
@ywlb varchar(20),
@sfks varchar(20), --1：省内； 2：省外
@body varchar(5000) output
as

declare @memberId varchar(max) = ''
declare @inpatientNo varchar(max) = ''
declare @admissionDate varchar(max) = ''
declare @admissionDepartments varchar(max) = ''
declare @treatingPhysician varchar(max) = ''
declare @admissionStatus varchar(max) = ''
declare @diseaseCode varchar(max) = ''
declare @initialDiagnosis varchar(max) = ''
declare @surgeryCode varchar(max) = ''
declare @wardArea varchar(max) = ''
declare @wardNo varchar(max) = ''
declare @berthNo varchar(max) = ''
declare @isIncrease varchar(max) = ''
declare @isReferra varchar(max) = ''
declare @inpatientTypeOflocal varchar(max) = ''
declare @bxAccount varchar(max) = ''
declare @tel varchar(max) = ''
declare @bankCardNo varchar(max) = ''
declare @accountHolder varchar(max) = ''
declare @holderRelation varchar(max) = ''
declare @remark varchar(max) = ''
declare @bigDiseaseNo varchar(max) = ''
declare @isReferraNo varchar(max) = ''
declare @disList varchar(max) = ''
declare @disCode varchar(max) = ''
declare @uploadType varchar(max) = ''
declare @familySysno varchar(max) = ''
declare @Stature varchar(max) = ''
declare @Weight varchar(max) = ''
declare @secondIcdNo varchar(max) = ''
declare @threeIcdNo varchar(max) = ''
declare @opsId varchar(max) = ''
declare @treatCode varchar(max) = ''
declare @cureId varchar(max) = ''
declare @complication varchar(max) = ''
declare @turnMode varchar(max) = ''
declare @turnCode varchar(max) = ''
declare @turnDate varchar(max) = ''
declare @ticketNo varchar(max) = ''
declare @ministerNotice varchar(max) = ''
declare @procreateNotice varchar(max) = ''
declare @isNewborn varchar(max) = ''
declare @newbornBirthday varchar(max) = ''
declare @newbornName varchar(max) = ''
declare @newbornSex varchar(max) = ''
declare @registerID varchar(max) = ''
declare @inpatientSn varchar(max) = ''
declare @areaCode varchar(max) = ''
declare @isTransProvincial varchar(max) = ''


if @sfks = '1'
begin
select @body = convert(varchar(max),(select @memberId as memberId,
@inpatientNo as inpatientNo,
@admissionDate as admissionDate,
@admissionDepartments as admissionDepartments,
@treatingPhysician as treatingPhysician,
@admissionStatus as admissionStatus,
@diseaseCode as diseaseCode,
@initialDiagnosis as initialDiagnosis,
@surgeryCode as surgeryCode,
@wardArea as wardArea,
@wardNo as wardNo,
@berthNo as berthNo,
@isIncrease as isIncrease,
@isReferra as isReferra,
@inpatientTypeOflocal as inpatientTypeOflocal,
@bxAccount as bxAccount,
@tel as tel,
@bankCardNo as bankCardNo,
@accountHolder as accountHolder,
@holderRelation as holderRelation,
@remark as remark,
@bigDiseaseNo as bigDiseaseNo,
@isReferraNo as isReferraNo
for xml path('')) )
return @body
end

返回值有问题 报错如下：

在将 varchar 值 '<memberId></memberId><inpatientNo></inpatientNo><admissionDate></admissionDate><admissionDepartments></admissionDepartments><treatingPhysician></treatingPhysician><admissionStatus></admissionStatus><diseaseCode></diseaseCode><initialDiagnosis></initialDiagnosis><surgeryCode></surgeryCode><wardArea></wardArea><wardNo></wardNo><berthNo></berthNo><isIncrease></isIncrease><isReferra></isReferra><inpatientTypeOflocal></inpatientTypeOflocal><bxAccount></bxAccount><tel></tel><bankCardNo></bankCardNo><accountHolder></accountHolder><holderRelation></holderRelation><remark></remark><bigDiseaseNo></bigDiseaseNo><isReferraNo></isReferraNo><disList><valueList><disCode></disCode></valueList></disList><uploadType></uploadType>' 转换成数据类型 int 时失败。


请问这是为什么呢？我的返回值类型是varchar的

[WorldMobile]

调试时别弄那么多字段，用一个字段试试，或者用个字符串常量

[李哈哈]

引用 1 楼 lzp_lrp 的回复:

调试时别弄那么多字段，用一个字段试试，或者用个字符串常量

找到原因了 直接参数用output就可以返回，不需要return

[李哈哈]

引用 1 楼 lzp_lrp 的回复:

调试时别弄那么多字段，用一个字段试试，或者用个字符串常量

而且 存储的返回值 好像必须是数值型 或者能转化成int类型的字符串，比如 ‘1’这种就可以 ，不能转换的就会报那个错误。