存储过程中的IF

[yyixin]

ALTER PROCEDURE [dbo].[test] 
@c_id int,
AS


BEGIN
 SELECT * FROM aaa WHERE bbb=222 and ccc=333 
END

要求当@c_id=0时 SELECT 为SELECT * FROM aaa WHERE bbb=222 and ccc=333 ，当大于0时为 SELECT * FROM aaa WHERE bbb=222 and ddd=444 and fff=555;

要求用“DECLARE @cwhere varchar(250) ”变量来进行赋值

[sdhdy]

--要求用“DECLARE @cwhere varchar(250) ”变量来进行赋值????????????????
ALTER PROCEDURE [dbo].[test] 
@c_id int,
AS


BEGIN
 if @c_id=0
 SELECT * FROM aaa WHERE bbb=222 and ccc=333 
 if @c_id>0
 SELECT * FROM aaa WHERE bbb=222 and ddd=444 and fff=555

END

[yyixin]

ALTER PROCEDURE [dbo].[test] 
@c_id int,
AS
 
DECLARE @cwhere varchar(250)
if @c_id>0
SET @cwhere = 'bbb=222 and ddd=444 and fff=555'
 if @c_id=0
SET @cwhere='bbb=222 and ccc=333 '
BEGIN


 SELECT * FROM aaa WHERE @cwhere

END

要求这样，但好像不行。

[sdhdy]

ALTER PROCEDURE [dbo].[test] 
@c_id int
AS
 
DECLARE @cwhere varchar(250)
if @c_id>0
SET @cwhere = 'bbb=222 and ddd=444 and fff=555'
 if @c_id=0
SET @cwhere='bbb=222 and ccc=333 '
BEGIN

----*************************************
exec('SELECT * FROM aaa WHERE '+ @cwhere)

END

[htl258_Tony]

ALTER PROCEDURE [dbo].[test] 
@c_id int,
AS
DECLARE @cwhere varchar(250)
if @c_id>0
  begin 
    SET @cwhere = 'bbb=222 and ddd=444 and fff=555'
    exec('SELECT * FROM aaa WHERE '+@cwhere)
  end
else if @c_id=0
  begin
    SET @cwhere='bbb=222 and ccc=333 '
    exec('SELECT * FROM aaa WHERE '+@cwhere)
  end

[yyixin]

谢谢大家，但我的
BEGIN ……END之间有“;WITH Liang AS(……)”用加上IF或exec会出错

[sdhdy]

WITH Liang AS(……)”

SQL2005的东西？？

[babygehui]

ALTER PROCEDURE [dbo].[test]
@c_id int,
AS

DECLARE @cwhere varchar(250)
DECLARE @sql varchar(250)
if @c_id>0
begin
SET @cwhere = 'bbb=222 and ddd=444 and fff=555'
if @c_id=0
begin
SET @cwhere='bbb=222 and ccc=333 '
end
SET @sql = ('SELECT * FROM aaa WHERE '+@cwhere )
execute sp_executesql @sql
END