这段代码在存储过程中为啥不能执行呢?总是报向变量赋值的 SELECT 语句不能与数据检索操作结合使用.脱离存储过程是可以执行的。
CREATE PROCEDURE selectindex
@indexzhi varchar,
@pid int,
@prname varchar(500)
AS
BEGIN
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like @indexzhi+'%' order by g.sort
IF (@prname is null)
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like '%'+@indexzhi+'%' order by g.sort
ELSE
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like ''+@indexzhi+'%' order by g.sortEND
CREATE PROCEDURE selectindex
@indexzhi varchar,
@pid int,
@prname varchar(500)
AS
BEGIN
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like @indexzhi+'%' order by g.sort
IF (@prname is null)
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like '%'+@indexzhi+'%' order by g.sort
ELSE
SELECT distinct g.id, @prname=g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like ''+@indexzhi+'%' order by g.sortEND
SELECT distinct g.id, g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like @indexzhi+'%' order by g.sort
这个结果集是不是空 如果不是空就原样显示 如果是空 就在模糊查询处在加一个%
@indexzhi varchar,
@pid int,
@prname varchar(500)
AS
BEGIN
--@pid貌似没用上。
if not exists(SELECT g.productName FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like @indexzhi+'%' and @prname=g.productName ) SELECT distinct g.id, g.productName, g.sort, g.dirType, i.title
FROM Goods g , indexProduct i where g.id = i.pid and i.indexk like '%'+@indexzhi+'%' order by g.sort
ELSE
SELECT distinct g.id, g.productName, g.sort, g.dirType,
i.title
FROM Goods g ,
indexProduct i where g.id = i.pid and i.indexk like ''+@indexzhi+'%' and @prname=g.productName order by g.sort END