dropprocedure[dbo].[pinfo5000]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[pro]')andOBJECTPROPERTY(id,N'IsProcedure')=1)

dropprocedure[dbo].[pro]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[sysconstraints]')andOBJECTPROPERTY(id,N'IsView')=1)

dropview[dbo].[sysconstraints]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[priduct]')andOBJECTPROPERTY(id,N'IsUserTable')=1)

droptable[dbo].[priduct]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[project]')andOBJECTPROPERTY(id,N'IsUserTable')=1)

droptable[dbo].[project]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[tblsales]')andOBJECTPROPERTY(id,N'IsUserTable')=1)

droptable[dbo].[tblsales]

GO

ifexists(select*fromdbo.systypeswherename=N'tele')

execsp_droptypeN'tele'

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[rule_position]')andOBJECTPROPERTY(id,N'IsRule')=1)

droprule[dbo].[rule_position]

GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[@position]')andOBJECTPROPERTY(id,N'IsDefault')=1)

dropdefault[dbo].[@position]

GO

createdefault[@position]as'其它'

GO

createrule[rule_position]as@postionin('项目经理','秘书','会计','职员','其它')

GO

setuser

GO

EXECsp_addtypeN'tele',N'smallint',N'notnull'

GO

setuser

GO

CREATETABLE[dbo].[priduct](

[产品ID][int]NULL,

[产品名称][char](20)COLLATEChinese_PRC_CI_ASNULL

)ON[PRIMARY]

GO

CREATETABLE[dbo].[project](

[项目编号][int]NOTNULL,

[项目名称][char](10)COLLATEChinese_PRC_CI_ASNOTNULL,

[开始日期][datetime]NULL,

[预计日期][int]NULL,

[客户编号][int]NULL,

[负责人编号][int]NULL,

[项目标的][int]NULL

)ON[PRIMARY]

GO

CREATETABLE[dbo].[tblsales](

[number][int]NOTNULL,

[name][varchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,

[sex][char](2)COLLATEChinese_PRC_CI_ASNULL,

[birthday][datetime]NULL,

[salary][money]NULL

)ON[PRIMARY]

GO

setuser

GO

EXECsp_bindefaultN'[dbo].[@position]',N'[project].[项目标的]'

GO

EXECsp_bindruleN'[dbo].[rule_position]',N'[project].[项目名称]'

GO

setuser

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_NULLSON

GO

--/******Encryptedobjectisnottransferable,andscriptcannotbegenerated.******/

GO

SETQUOTED_IDENTIFIEROFF

GO

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_NULLSON

GO

createproceduregetavgpbiaodi

@namevarchar(10),@avgpbiaodiintoutput

as

declare@errorsaveint

set@errorsave=0

select@avgpbiaodi=avg(项目标的)

fromproject aspinnerjoinpmanageraspmonp.负责人ID=pm.负责人ID

wherepm.姓名=@name