存储过程

create proc Pro_GetNews
as
select * from T_News;
GO
EXEC Pro_GetNews
--参数名称一定要加@前缀
CREATE PROC Pro_GetNewsByTitle
@newstitle varchar(64)
as
select NewsTitle,SUBSTRING(NewsContent,1,20)+'....' AS NewsContent,CreateTime FROM T_News
WHERE NewsTitle like @newstitle;
GO

EXEC Pro_GetNewsByTitle '%航母%'

--存储过程，根据内容和标题匹配数据
CREATE PROC Pro_GetNewsByTitleAndContent
@newskey VARCHAR(8)
AS
SELECT T1.NewsTitle,SUBSTRING(T1.NewsContent,1,20)+'.....'AS NewsContent,T1.CreateTime FROM T_News T1
GO
EXEC Pro_GetNewsByTitleANDTitle '%国%'
GO
--新建存储过程，向新闻表插入一条数据，并返回自动生成的最大编号
CREATE PROC Pro_GetMaxId @maxid INT OUT
AS
INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
VALUES
('本周全国大部分地区降温','本周全国大部分地区降温，最高降温达10°','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5');
SET @maxid=@@IDENTITY
GO

DECLARE @maxid INT;
EXEC Pro_GetMaxId @maxid OUT;
PRINT @maxid;

--接收用户输入的参数，插入数据，返回最大编号   五个输入参数，一个输出参数
CREATE PROC Pro_GetMaxId1
@newstitle varchar(64),
@newscontent varchar(max),
@newscreator varchar(8),
@createtime datetime,
@classname varchar(8),
@maxid INT OUT
AS
DECLARE @classid uniqueidentifier;
select @classid=T1.ClassId FROM T_NewsClass T1 WHERE T1.ClassName=@classname;
INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
VALUES
(@newstitle,@newscontent,@newscreator,GETDATE(),@classid)
SET @maxid=@@IDENTITY;
Go
--执行存储过程
DECLARE @maxid INT;
EXEC Pro_GetMaxId1 '111','11111111','004','1900-02-02','军事',@maxid OUT;
print @maxid;
GO