CREATE PROCEDURE sp_News_GetNews
@CategoryID int,
@CurrentApprovedOnly bit,
@AbstractLength int
AS-- if @AbractLegth is not -1 return the first @AbstractLength chars of the Body field, otherwise return the whole news body
IF @AbstractLength <> -1
BEGIN
IF @CurrentApprovedOnly = 1
BEGIN
SELECT NewsID, Title, LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID AND Approved = 1 AND ReleaseDate <= GETDATE() AND ExpireDate >= GETDATE()
END
ELSE
BEGIN
SELECT NewsID, Title, LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID
END
END
ELSE
BEGIN
IF @CurrentApprovedOnly = 1
BEGIN
SELECT NewsID, Title, Body AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID AND Approved = 1 AND ReleaseDate <= GETDATE() AND ExpireDate >= GETDATE()
END
ELSE
BEGIN
SELECT NewsID, Title, Body AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID
END
END
@CategoryID int,
@CurrentApprovedOnly bit,
@AbstractLength int
AS-- if @AbractLegth is not -1 return the first @AbstractLength chars of the Body field, otherwise return the whole news body
IF @AbstractLength <> -1
BEGIN
IF @CurrentApprovedOnly = 1
BEGIN
SELECT NewsID, Title, LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID AND Approved = 1 AND ReleaseDate <= GETDATE() AND ExpireDate >= GETDATE()
END
ELSE
BEGIN
SELECT NewsID, Title, LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID
END
END
ELSE
BEGIN
IF @CurrentApprovedOnly = 1
BEGIN
SELECT NewsID, Title, Body AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID AND Approved = 1 AND ReleaseDate <= GETDATE() AND ExpireDate >= GETDATE()
END
ELSE
BEGIN
SELECT NewsID, Title, Body AS Abstract, ReleaseDate, ExpireDate, AddedDate, Approved, News_News.UserID, (FirstName + ' ' + LastName) AS UserName, EmailAddress AS UserEmail
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID
END
END
解决方案 »
- 弹出新窗口,如何关闭父窗口??
- .show("fast"); //设置x坐标和y坐标,并且显示 show jquery 自带的函数么
- 一般的网络错误。检查您的网络文档。
- 使用母版页的问题请教~!!!
- 关于频繁的数据读取问题
- Access数据库为什么更新之后数据却没改变?
- 在线等!!!!!!!
- 同时打开连接时会抛出连接已经打开异常,怎样避免这样的问题?
- asp.net 如何设置RadioButtonList的多选。。、、、
- 将这句C#代码转成VB.NET代码.int.Parse(((System.Data.DataRowView)e.Item.DataItem)["id"].ToString())).DefaultView
- 大家帮我看看我的这个菜鸟问题。。。。。谢谢。
- 很简单的问题,谁能帮一下?
FROM News_News LEFT JOIN Accounts_Users
ON News_News.UserID = Accounts_Users.UserID
WHERE CategoryID = @CategoryID AND Approved = 1 AND ReleaseDate <= GETDATE() AND ExpireDate >= GETDATE()这一段是什么意思? 中间的LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract 和 (FirstName + ' ' + LastName) AS UserName 是什么意思?
至于那条sql语句, 你可以查下 left函数, 和cast函数!
这样看好象没什么问题呀
-- if @AbractLegth is not -1 return the first @AbstractLength chars of the Body field, otherwise return the whole news body这段话前面加了"--"表示注释,不执行2、
这一段是什么意思? 中间的LEFT(CAST(Body AS varchar(1000)), @AbstractLength) + '...' AS Abstract 和 (FirstName + ' ' + LastName) AS UserName 是什么意思?CAST(Body AS varchar(1000)) 把字段Body转换为Varchar型
Left(LEFT(CAST(Body AS varchar(1000)), @AbstractLength)) 从左开始取字段body的@AbstractLength长度的内容(CAST(Body AS varchar(1000)), @AbstractLength) + '...' 把取出的内容后面再加上"..."(FirstName + ' ' + LastName) AS UserName 把字段FirstName和字段LastName连接起来用空格_隔开.(AS UserName UserName为取出内容的别名)
如果@AbractLegth !=-1返回@AbractLegth 体的第一个字符,其他的返回全部Left(LEFT(CAST(Body AS varchar(1000)), @AbstractLength)) 从左开始取字段body的@AbstractLength长度的内容