请教:存储过程,很菜的问题?
下面的存储过程语法无错,但就是查找不出记录?
CREATE PROCEDURE ST_ShowRoomByCatgAndStatus2(
@RCategoryId int,@Status int)
AS
DECLARE @str nvarchar(1000)
if @RCategoryId!=0
BEGIN
SET @str = @str + ' and r.ST_RCategoryId= ' + @RCategoryId
END
if @Status!=0
BEGIN
SET @str = @str + ' and s.ST_Status= ' + @Status
END
select r.ST_RoomId, c.ST_Name,s.ST_Status
from ST_RoomsInfo r, ST_RoomCategory c, ST_RoomStatus s
where s.ST_RoomId=r.ST_RoomId
and c.ST_RCategoryId=r.ST_RCategoryId + ' '+ @str
GO
[解决办法]
CREATE PROCEDURE ST_ShowRoomByCatgAndStatus2(
@RCategoryId int,@Status int)
AS
DECLARE @str nvarchar(1000)
set @str= ' '--要赋初值,不然为NULL
if @RCategoryId!=0
BEGIN
SET @str = @str + ' and r.ST_RCategoryId= ' + rtrim(@RCategoryId)--转为字符型再相加
END
if @Status!=0
BEGIN
SET @str = @str + ' and s.ST_Status= ' + rtrim(@Status)
END
select @str= '
select r.ST_RoomId, c.ST_Name,s.ST_Status
from ST_RoomsInfo r, ST_RoomCategory c, ST_RoomStatus s
where s.ST_RoomId=r.ST_RoomId
and c.ST_RCategoryId=r.ST_RCategoryId '+ @str
exec(@str)
GO
[解决办法]
NULL与任何变量的算术运算结果还是NULL。
