ALTER proc GETTYPELIST
@userid int
AS
DECLARE @listall varchar(100)=''
DECLARE @owntype varchar(100)=''
SELECT @owntype=othertype FROM [H_user] WHERE ID=279
return @owntype
GO
EXEC [GETTYPELIST] 279
执行的时候
提示错误 消息245,级别16,状态1,过程GETTYPELIST,第7 行
在将varchar 值'6,12,16,14,12' 转换成数据类型int 时失败。
@userid int
AS
DECLARE @listall varchar(100)
set @listall=''
DECLARE @owntype varchar(100)
set @owntype=''
SELECT @owntype=othertype FROM [H_user] WHERE ID=279
return @owntype
GO
EXEC [GETTYPELIST] 279
@userid varchar(100) --这里类型要改改****************
AS
DECLARE @listall varchar(100)=''
DECLARE @owntype varchar(100)=''
SELECT @owntype=othertype FROM [H_user] WHERE ID=279
return @owntype
GO
(
@userid int
)
AS
begin
DECLARE @listall varchar(100)
DECLARE @owntype varchar(100)
select @listall='', @owntype=''
SELECT @owntype=othertype FROM [H_user] WHERE ID=279
return @owntype
end
GO
EXEC [GETTYPELIST] 279
改成 select @owntype 看一下?
(
@userid int
)
AS
begin
DECLARE @owntype varchar(100)
SELECT @owntype=othertype FROM [H_user] WHERE [ID]=@userid
return @owntype
end
GO
EXEC [GETTYPELIST] 279
[H_user] 表里面 othertype 这个字段是nvarchar(100)
按照4楼所说
查询结果就是 6,12,16,14,12
环境是sql2008
现在还是提示 消息245,级别16,状态1,过程GETTYPELIST,第7 行
在将varchar 值'6,12,16,14,12' 转换成数据类型int 时失败。
改为:select @owntype
othertype里面的值'6,12,16,14,12'
(
@userid int
)
AS
begin
DECLARE @owntype varchar(100)
SELECT @owntype=othertype FROM [H_user] WHERE [ID]=@userid
select @owntype
end
GO EXEC [GETTYPELIST] 279
这个我明白
但是为什么不能用return 返回呢 ?
@userid int,
@owntype nvarchar(100) OUTPUT
AS
DECLARE @listall varchar(100)=''
DECLARE @owntype varchar(100)=''
SELECT @owntype=othertype FROM [H_user] WHERE ID=279
--return @owntype return的值必须是INT
GO --调用
DECLARE @type nvarchar(100)
EXEC 279,@type = @owntype OUTPUT
SELECT @type