sqlserver 存储过程和自定义函数

sqlserver 存储过程

create procedure proc_menu

@menuIdvarchar(50),

@menuTotalintoutput

as

begin

setnocounton;

select@menuTotal=count(*)fromdbo.Lov_menutwheret.menu_id=@menuId;

end

sqlserver 自定义函数

create function Sector_Name(@sctId varchar(10))

returnsvarchar(50)

as

begin

declare@ccodevarchar(50)

declare@namevarchar(50)

declare@pidvarchar(10)

declare@sctpidvarchar(10)

declare @spid   varchar(10)

select @pid=SCT_PID  from SECTOR where SCT_ID=@sctId

if(@pid=1)

set@ccode=(selectSCT_NAMfromSECTORwhereSCT_ID=@sctId)

elseif(@pid=0)

set@ccode=(selectSCT_NAMfromSECTORwhereSCT_ID=@sctId)

else

begin

select@name=SCT_NAMfromSECTORwhereSCT_ID=@sctId

select@sctpid=SCT_PIDfromSECTORwhereSCT_ID=@pid

if(@sctpid=1)

set@ccode=(selectSCT_NAMfromSECTORwhereSCT_ID=@pid)+'-'+@name

else

begin

while@sctpid<>1

begin

select@spid=SCT_PIDfromSECTORwhereSCT_ID=@sctpid

set@ccode=(selectSCT_NAMfromSECTORwhereSCT_ID=@sctpid)+'-'+@name

set@sctpid=@spid

if@sctpid=1

break

end

end

end

return@ccode

end