关于存储过程设置默认值



如果要在程序中调用存储过程而又没法给参数传值的话,而sqlserver 存储过程参数没有设置默认值的话,则会抛出异常,可以采用给sqlserver 存储过程设置默认值,如果程序没传入参数值,则使用默认缺省值,这样就不会抛出异常了

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO 


ALTER PROC [dbo].[P_Message_user_view] 
--exec P_Message_user_view120904 '20111001','20121002','',null,null,-1,null,12,1,null 
@i_begintime VARCHAR(100) =null@i_endtime VARCHAR(100) =null@i_title VARCHAR(100) =null@i_mtid INT =null,--类型id 
@i_levelid INT =null, --等级id 
@i_depid INT =null@i_userid INT =null@i_pagecount INT =null@i_currentpage INT =null ,     //这些
@i_messageid INT = NULL@i_isclient int = NULL 
AS 
BEGIN 
IF @i_userid IS NULL 
BEGIN 
SET @i_userid=10089 
END 
IF @i_pagecount IS NULL 
BEGIN 
SET @i_pagecount=10000 
END 
IF @i_currentpage IS NULL 
BEGIN 
SET @i_currentpage=10000 
END 

IF @i_begintime IS NULL 
BEGIN 
SET @i_begintime='19000101' 
END 
IF @i_endtime IS NULL 
BEGIN 
SET @i_endtime='20200101' 
END 
IF @i_title IS NULL 
BEGIN 
SET @i_title='' 
END 
DECLARE @v_beginmtid INT,@v_endmtid INT ,@v_begindepid INT ,@v_enddepid INT ,@v_beginlevelid INT, @v_endlevelid INT 
IF @i_mtid IS NULL 
BEGIN 
SET @v_beginmtid=0 
SET @v_endmtid=10000 
END 
ELSE 
BEGIN 
SELECT @v_beginmtid=@i_mtid,@v_endmtid=@i_mtid+1 
END 
IF @i_levelid IS NULL 
BEGIN 
SET @v_beginlevelid=0 
SET @v_endlevelid=10000 
END 
ELSE 
BEGIN 
SELECT @v_beginlevelid=@i_levelid,@v_endlevelid=@i_levelid+1 
END 
CREATE table #dep (depid INTDECLARE @v_sqlstr VARCHAR(8000),@v_sqlstrx VARCHAR(8000),@v_ishot VARCHAR(100set @v_ishot='' 
IF @i_depid IS NULL 
BEGIN 
INSERT INTO #dep 
SELECT id FROM Department d WHERE d.DeletedFlag=0 
END 
IF @i_depid=0 
BEGIN 
INSERT INTO #dep 
SELECT id FROM Department d WHERE d.DeletedFlag=0 
END 
IF @i_depid=-1 
BEGIN 
INSERT INTO #dep 
SELECT id FROM Department d WHERE d.DeletedFlag=0 
SET @v_ishot=' and M.ishot=1' 
END 
IF @i_depid>0 
BEGIN 
DECLARE @v_depcode VARCHAR(100SELECT @v_depcode=depcode 
FROM Department d WHERE id=@i_depid 

INSERT INTO #dep 
SELECT id 
FROM Department d 
WHERE LEFT(d.depcode,LEN(@v_depcode))=@v_depcode 
END 


CREATE TABLE #count( COUNTs INTSELECT @v_sqlstr=' 
insert into #count 
select count(M.id) from ' 
+tablename +' UT ,Message M,#dep dep 
where UT.userid=' 
+convert(varchar(10),@i_USERID) 
+' 
and UT.messageid=M.id 
and M.deletedflag=0 
and UT.deletedflag=0 
and m.departmentid=dep.depid' 
+@v_ishot+' 
and m.mtid>='+CONVERT(VARCHAR(100),@v_beginmtid)+' 
and m.mtid<'+CONVERT(VARCHAR(100),@v_endmtid)+' 
and m.levelid>='+CONVERT(VARCHAR(100),@v_beginlevelid)+' 
and m.levelid<'+CONVERT(VARCHAR(100),@v_endlevelid)+' 
and m.modifydate>='+''''+@i_begintime+''' 
and m.modifydate<'+''''+@i_endtime+''' 
and m.title like ''%'+@i_title+'%'' 
',@v_sqlstrx=' 
insert into #showall 
(messageID,MTID,Title,isAttachment,DepartmentID,LevelID,IsRead,ModifyDate,IsHot) 
select top '+CONVERT(varchar(100),@i_pagecount*@i_currentpage)+' 

M.ID,M.MTID,M.Title,M.isAttachment,M.DepartmentID,M.LevelID,UT.IsRead,M.ModifyDate,M.IsHot 
from ' 
+tablename +' UT ,Message M,#dep dep 
where UT.userid=' 
+convert(varchar(10),@i_USERID) 
+' 
and UT.messageid=M.id 
and M.deletedflag=0 
and UT.deletedflag=0' 
+@v_ishot+' 
and m.departmentid=dep.depid 
and m.mtid>='+CONVERT(VARCHAR(100),@v_beginmtid)+' 
and m.mtid<'+CONVERT(VARCHAR(100),@v_endmtid)+' 
and m.levelid>='+CONVERT(VARCHAR(100),@v_beginlevelid)+' 
and m.levelid<'+CONVERT(VARCHAR(100),@v_endlevelid)+' 
and m.modifydate>='+''''+@i_begintime+''' 
and m.modifydate<'+''''+@i_endtime+''' 
and m.title like ''%'+@i_title+'%'''+ 
' ORDER BY M.ModifyDate DESC 
' 
FROM ADM_F_GETUSERREADTABLE (@i_USERID) 
--print @v_sqlstr 


--print @v_sqlstr 
EXEC (@v_sqlstr) 
--SELECT * FROM #count 

CREATE TABLE #showall 
( 
ids INT IDENTITY, 
messageID INT,-- 
MTID INT,-- 
Title VARCHAR(1000), 
isAttachment INT, 
DepartmentID INT, 
LevelID INT, 
IsRead INT, 
ModifyDate DATETIME, 
IsHot int 
) 
--print @v_sqlstrx 
EXEC (@v_sqlstrx) 
--	SELECT * FROM #showall 
IF @i_messageid IS NULL 
BEGIN 
IF @i_isclient IS NOT NULL 
BEGIN 
SELECT distinct ISNULL(@i_depid,0) AS rootdeptid,c.counts as msgcount, 
--a.ids, 
a.messageID as messageid,a.MTID as mtid, 
CASE WHEN m.needprove = '1' THEN '证' end 
a.Title as title, 
a.isAttachment as isattachment,a.DepartmentID as deptid, 
a.LevelID as levelid,a.IsRead as isread, 
a.ModifyDate as modifydate,a.IsHot as ishot,m.needprove 
FROM #showall a,#count c ,dbo.Message m 
WHERE m.ID = a.messageID AND a.ids>=(@i_currentpage-1)*@i_pagecount+1 
AND a.ids< (@i_currentpage+1)*@i_pagecount 
order by a.ModifyDate desc 
END 
ELSE 
SELECT distinct ISNULL(@i_depid,0) AS rootdeptid,c.counts as msgcount, 
--a.ids, 
a.messageID as messageid,a.MTID as mtid,a.Title as title, 
a.isAttachment as isattachment,a.DepartmentID as deptid, 
a.LevelID as levelid,a.IsRead as isread, 
a.ModifyDate as modifydate,a.IsHot as ishot,m.needprove 
FROM #showall a,#count c ,dbo.Message m 
WHERE m.ID = a.messageID AND a.ids>=(@i_currentpage-1)*@i_pagecount+1 
AND a.ids< (@i_currentpage+1)*@i_pagecount 
order by a.ModifyDate desc 
END 
END 
ELSE 
BEGIN 
DECLARE @v_currentids INT,@v_prevmsgid INT,@v_nextmsgid INT 
select @v_currentids=ids FROM #showall WHERE messageID=@i_messageid 
SELECT @v_prevmsgid=messageid FROM #showall WHERE ids=@v_currentids-1 
SELECT @v_nextmsgid=messageid FROM #showall WHERE ids=@v_currentids+1 
SELECT distinct M.*,@v_prevmsgid AS prevmsgid, 
@v_nextmsgid AS nextmsgid, 
shall.IsRead as isread INTO #shw 
FROM MESSAGE M,#showall shall 
WHERE M.id=@i_messageid 
AND m.ID=shall.messageID 

IF EXISTS( SELECT 1 FROM #shw WHERE isread=0BEGIN 
exec [P_Message_User_Update_for_view] @i_userid,@i_messageid 

UPDATE [Message] 
SET Hits =ISNULL(hits,0)+1 
WHERE id=@i_messageid 
END 
SELECT * FROM #shw 
END 

END 
GO 


posted on   signheart  阅读(3933)  评论(0编辑  收藏  举报

编辑推荐:
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
阅读排行:
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· DeepSeek火爆全网,官网宕机?本地部署一个随便玩「LLM探索」
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 上周热点回顾(1.20-1.26)

导航

< 2025年1月 >
29 30 31 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示