CREATE PROCEDURE query_vendition_rec_sp
@bookno VARCHAR(10),--书号
@bookname VARCHAR(50),--书名
@vtime DATETIME, --销售时间
@mno VARCHAR(10) --会员号
AS
IF (@mno = '')
BEGIN
SELECT Vno , Books.Bno , Books.Bname , Vprice ,
Vcount, Vtime , Ename , Bdepot
FROM Books , Vendition , Employee
WHERE Books.Bno LIKE '%' + @bookno + '%' AND Books.Bname LIKE '%' + @bookname + '%'
AND Vtime LIKE '%' + @vtime + '%' AND Vendition.Bno = Books.Bno
AND Vendition.Eno = Employee.Eno
ORDER BY Books.Bno DESC
END执行的时候是不是必须得用4个参数,这样能只用一个参数就执行查询吗?也就是把其他的参数设置为空,可以吗?
我试了'',但是提示说:
消息 137,级别 15,状态 1,第 3 行
必须声明标量变量 "@bookname"。
消息 137,级别 15,状态 1,第 4 行
必须声明标量变量 "@vtime"。
消息 137,级别 15,状态 1,第 5 行
必须声明标量变量 "@mno"。
该如何解决呢?
难道为每一种查询方法设置一个存储过程?
@bookno VARCHAR(10)='',--书号
@bookname VARCHAR(50)='',--书名
@vtime DATETIME='', --销售时间
@mno VARCHAR(10)='' --会员号
AS
IF (@mno = '')
BEGIN
SELECT Vno , Books.Bno , Books.Bname , Vprice ,
Vcount, Vtime , Ename , Bdepot
FROM Books , Vendition , Employee
WHERE Books.Bno LIKE '%' + @bookno + '%' AND Books.Bname LIKE '%' + @bookname + '%'
AND Vtime LIKE '%' + @vtime + '%' AND Vendition.Bno = Books.Bno
AND Vendition.Eno = Employee.Eno
ORDER BY Books.Bno DESC
END
@bookno VARCHAR(10)=null,--书号
@bookname VARCHAR(50)=null,--书名
@vtime DATETIME=null, --销售时间
@mno VARCHAR(10)=null --会员号
AS
..
where Books.Bno LIKE '%' + isnull(@bookno,Books.Bno) + '%' AND Books.Bname LIKE '%' + isnull(@bookname,Books.Bname) + '%'
AND Vtime LIKE '%' + isnull(@vtime,Vtime) + '%' AND Vendition.Bno = Books.Bno
AND Vendition.Eno = Employee.Eno
SET @bookno = '0000001';
EXECUTE dbo.query_vendition_rec_sp @bookno --执行存储过程可是执行的时候说从字符串转化为datetime失败
@bookno VARCHAR(10) = null,--书号
@bookname VARCHAR(50) = null,--书名
@vtime DATETIME = null , --销售时间
@mno VARCHAR(10) = null --会员号
AS
IF (@mno = '')
BEGIN
SELECT Vno , Books.Bno , Books.Bname , Vprice ,
Vcount, Vtime , Ename , Bdepot
FROM Books , Vendition , Employee
WHERE Books.Bno LIKE '%' + @bookno + '%' AND Books.Bname LIKE '%' + @bookname + '%'
AND Vtime LIKE '%' + @vtime + '%' AND Vendition.Bno = Books.Bno
AND Vendition.Eno = Employee.Eno
ORDER BY Books.Bno DESC
END传参数的时候 可以把某个传成默认值。
@bookno VARCHAR(10),--书号
@bookname VARCHAR(50),--书名
@vtime DATETIME, --销售时间
@mno VARCHAR(10) --会员号
)//这里漏了)
AS
IF (@mno = '')
BEGIN
SELECT Vno , Books.Bno , Books.Bname , Vprice ,
Vcount, Vtime , Ename , Bdepot
FROM Books , Vendition , Employee
WHERE Books.Bno LIKE '%' + @bookno + '%' AND Books.Bname LIKE '%' + @bookname + '%'
AND Vtime LIKE '%' + @vtime + '%' AND Vendition.Bno = Books.Bno
AND Vendition.Eno = Employee.Eno
ORDER BY Books.Bno DESC
END
根据LZ的要求, 对于char型用'%'默认值, 对时间类型用'1900-01-01'或getdate()
在调用存储过程时, 只带一个参数运行就行了