ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[insert_song])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[insert_song]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_song_list])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_song_list]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_wawa_song])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_wawa_song]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[wawa_song])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[wawa_song]
GO
CREATETABLE[dbo].[wawa_song](
[song_id][int]IDENTITY(1,1)NOTNULL,
[song_name][char](40)COLLATEChinese_PRC_CI_ASNOTNULL,
[song_content][varchar](4000)COLLATEChinese_PRC_CI_ASNOTNULL,
[song_author][char](20)COLLATEChinese_PRC_CI_ASNULL,
[author_id][int]NULL
)ON[PRIMARY]
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
/*
历程check_song,经由过程@song_name变量来查询数据表中是不是有反复的纪录，假如有则设定@state这个输出参数的值为1,该值间接影响到addnew历程的运转
*/
createproccheck_song
@song_namechar(40),
@stateintoutput
as
begin
ifexists(selectsong_namefromwawa_song
wheresong_name=@song_name)
set@state=1
else
set@state=0
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
/*
历程insert_song
*/
CREATEprocinsert_song
@song_namechar(40),
@song_contentvarchar(4000),
@song_authorchar(20)
as
begin
declare@stateint
execcheck_song@song_name,@stateoutput
if@state=0
begin
begintran
insertintowawa_song(song_name,song_content,song_author)values(@song_name,@song_content,@song_author)
committran
raiserror(%s增加乐成！,16,1,@song_name)
end
else
begin
raiserror(用户名%s已存在！,16,1,@song_name)
return
end
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSOFF
GO
CREATEPROCEDURE[p_song]AS
select*fromwawa_songorderbysong_iddesc
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSOFF
GO
createprocp_wawa_song
@idint
as
selectsong_id,song_name,song_author,song_contentfromwawa_songwheresong_id=@id
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO