一個腦火的存儲過程出現的問題

wwwiii520 2008-12-02 10:27:22
[code=MSIL]
ALTER proc AddRevmeeting(@meetingId varchar(6),@fromtime datetime,@totime datetime,
@content varchar(50),@userName varchar(12),@node varchar(50),
@str varchar(200) output
)
as

DECLARE @rId char(6)
DECLARE @rmeetingId char(6)
DECLARE @rfromtime datetime
DECLARE @rtotime datetime
DECLARE @rcontent varchar(50)
DECLARE @ruserName varchar(12)

/*set @str=null*/
/*set nocount on*/
select @str=Id,@rmeetingId=meetingId,@rfromtime=fromtime,@rtotime=totime,
@rcontent=content,@ruserName=userName from Revmeeting where meetingId=@meetingId
/*and(fromtime<=@totime and totime>=@fromtime )*/
if(@@rowcount>0)
begin
set @str='預定發生衝突,沖突記錄如下:\r\n會議編號:'+@rId+
'\r\n會議室編號:'+@rmeetingId+
'\r\n預定時間:'+cast(@rfromtime as varchar)+
'\r\n結束時間:'+cast(@rtotime as varchar)+
'\r\n會議名稱:'+@rcontent+
'\r\n預定人:'+@ruserName+'.'
select @str
return 1
end
else
insert into Revmeeting values(@meetingId,@fromtime,@totime,
@content,@userName,@node)
[/code]

在if(@@rowcount>0)的情況下
我在C#里用output型參數獲取@str值.為什么總得到一個空值
而我把 修改成 set @str='預定發生衝突,沖突記錄如下:\r\n會議編號:'
又能獵取這個參數的值了.

現問題就是出現在 這段字符+變量值出現的問題
...全文
74 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
水族杰纶 2008-12-02
  • 打赏
  • 举报
回复
--try
ALTER proc AddRevmeeting(@meetingId varchar(6),@fromtime datetime,@totime datetime,
@content varchar(50),@userName varchar(12),@node varchar(50),
@str varchar(200) output
)
as

DECLARE @rId char(6)
DECLARE @rmeetingId char(6)
DECLARE @rfromtime datetime
DECLARE @rtotime datetime
DECLARE @rcontent varchar(50)
DECLARE @ruserName varchar(12)

/*set @str=null*/
/*set nocount on*/
select @str=isnull(Id,''),@rmeetingId=isnull(meetingId,''),@rfromtime=isnull(fromtime,''),@rtotime=isnull(totime,''),
@rcontent=isnull(content,''),@ruserName=isnull(userName,'') from Revmeeting where meetingId=@meetingId
/*and(fromtime<=@totime and totime>=@fromtime )*/
if(@@rowcount>0)
begin
set @str='預定發生衝突,沖突記錄如下:\r\n會議編號:'+@rId+
'\r\n會議室編號:'+@rmeetingId+
'\r\n預定時間:'+cast(@rfromtime as varchar)+
'\r\n結束時間:'+cast(@rtotime as varchar)+
'\r\n會議名稱:'+@rcontent+
'\r\n預定人:'+@ruserName+'.'
select @str
return 1
end
else
insert into Revmeeting values(@meetingId,@fromtime,@totime,
@content,@userName,@node)
wwwiii520 2008-12-02
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 hyde100 的回复:]
加isnull判断

SQL codeALTER proc AddRevmeeting(@meetingId varchar(6),@fromtime datetime,@totime datetime,
@content varchar(50),@userName varchar(12),@node varchar(50),
@str varchar(200) output
)
as

DECLARE @rId char(6)
DECLARE @rmeetingId char(6)
DECLARE @rfromtime datetime
DECLARE @rtotime datetime
DECLARE @rcontent varchar(50)
DECLARE @ruserName varchar(12)

/*set @str=null*…
[/Quote]

兄弟來親個..嘎嘎..
問題解決

結帖
dawugui 2008-12-02
  • 打赏
  • 举报
回复
动态SQL吗?参考如下:

动态sql语句基本语法
1 :普通SQL语句可以用Exec执行

eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

2:字段名,表名,数据库名之类作为变量时,必须用动态SQL

eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错

declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确

3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
csdyyr 2008-12-02
  • 打赏
  • 举报
回复
注意@rId
hyde100 2008-12-02
  • 打赏
  • 举报
回复
@id忘加了

ALTER proc AddRevmeeting(@meetingId varchar(6),@fromtime datetime,@totime datetime,
@content varchar(50),@userName varchar(12),@node varchar(50),
@str varchar(200) output
)
as

DECLARE @rId char(6)
DECLARE @rmeetingId char(6)
DECLARE @rfromtime datetime
DECLARE @rtotime datetime
DECLARE @rcontent varchar(50)
DECLARE @ruserName varchar(12)

/*set @str=null*/
/*set nocount on*/
select @str=Id,@rmeetingId=meetingId,@rfromtime=fromtime,@rtotime=totime,
@rcontent=content,@ruserName=userName from Revmeeting where meetingId=@meetingId
/*and(fromtime<=@totime and totime>=@fromtime )*/
if(@@rowcount>0)
begin
set @str='預定發生衝突,沖突記錄如下:\r\n會議編號:'+isnull(@rId,'')+
'\r\n會議室編號:'+isnull(@rmeetingId,'')+
'\r\n預定時間:'+cast(isnull(@rfromtime,'') as varchar)+
'\r\n結束時間:'+cast(isnull(@rtotime,'') as varchar)+
'\r\n會議名稱:'+isnull(@rcontent,'')+
'\r\n預定人:'+isnull(@ruserName,'')+'.'
select @str
return 1
end
else
insert into Revmeeting values(@meetingId,@fromtime,@totime,
@content,@userName,@node)
hyde100 2008-12-02
  • 打赏
  • 举报
回复
加isnull判断
ALTER   proc AddRevmeeting(@meetingId varchar(6),@fromtime datetime,@totime datetime,
@content varchar(50),@userName varchar(12),@node varchar(50),
@str varchar(200) output
)
as

DECLARE @rId char(6)
DECLARE @rmeetingId char(6)
DECLARE @rfromtime datetime
DECLARE @rtotime datetime
DECLARE @rcontent varchar(50)
DECLARE @ruserName varchar(12)

/*set @str=null*/
/*set nocount on*/
select @str=Id,@rmeetingId=meetingId,@rfromtime=fromtime,@rtotime=totime,
@rcontent=content,@ruserName=userName from Revmeeting where meetingId=@meetingId
/*and(fromtime<=@totime and totime>=@fromtime )*/
if(@@rowcount>0)
begin
set @str='預定發生衝突,沖突記錄如下:\r\n會議編號:'+@rId+
'\r\n會議室編號:'+isnull(@rmeetingId,'')+
'\r\n預定時間:'+cast(isnull(@rfromtime,'') as varchar)+
'\r\n結束時間:'+cast(isnull(@rtotime,'') as varchar)+
'\r\n會議名稱:'+isnull(@rcontent,'')+
'\r\n預定人:'+isnull(@ruserName,'')+'.'
select @str
return 1
end
else
insert into Revmeeting values(@meetingId,@fromtime,@totime,
@content,@userName,@node)
csdyyr 2008-12-02
  • 打赏
  • 举报
回复
检查你所加的变量是否有null
csdyyr 2008-12-02
  • 打赏
  • 举报
回复
没有初始化变量,或者变量null而用了+后会出现之后的问题
hyde100 2008-12-02
  • 打赏
  • 举报
回复
看看先

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧