22,300
社区成员




CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE b.keyWords like '%' +@keyWords + '%';
GO
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) like '%' +@keyWords + '%'
group by b.bookID,b.typename,b.bookname,b.publish,b.author;
GO
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) like '%' +@keyWords + '%';
GO
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*)
FROM bookinfo b
WHERE cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) like '%' +@keyWords + '%';
GO
exec sp_getTotalRecordsByKeyWords 'j'
返回无名列:结果集为2
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50), @count int output
AS
SELECT cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) like '%' +@keyWords + '%'
SET @count = @@ROWCOUNT
GO
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total, keyWords
FROM (SELECT cast(bookID as varchar(30))
+ cast(typename as varchar(30))
+ cast(bookname as varchar(50))
+ cast(publish as varchar(20))
+ cast(author as varchar(30)) AS keyWords
FROM bookinfo) b
WHERE keyWords like '%'+@keyWords+'%'
GROUP BY keyWords
GO
--好久没来mssql了
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b
WHERE (cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30))) like '%' +@keyWords + '%';
group by (cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)))
GO
--or
CREATE PROCEDURE sp_getTotalRecordsByKeyWords
@keyWords varchar(50)
AS
select total,keyWords
from (SELECT count(*) AS total,
cast(b.bookID as varchar(30))
+ cast(b.typename as varchar(30))
+ cast(b.bookname as varchar(50))
+ cast(b.publish as varchar(20))
+ cast(b.author as varchar(30)) AS keyWords
FROM bookinfo b) t
WHERE t.keyWords like '%' +@keyWords + '%';
GO