from 语句中的case 问题

[luckyTOTO]

我想实现下面的效果，不知怎么写？

select * from T1
case @Para1 when 1 then inner join T2
end
where .....
谢谢大家执教

[wangdehao]

能不能把问题写完整了？

[renjun24]

不会，帮顶

[huyouni]

declare @sql varchar(8000)
select @sql='select * from T1'+case @Para1 when 1 then +' inner join T2 on ...' end +' where .....'

[huyouni]

exec(@sql)

[luckyTOTO]

select * from T1
case @Para1 when 1 then inner join T2
end
where t1.id > 0

where 子句不用考虑

[luckyTOTO]

不用动态语句

[zlp321002]

select * from T1
case @Para1 when 1 then inner join T2
end
where .....
-------------------->>
if @Para1=1
begin
select * from T1 inner join t2
on ....
end

[wangdehao]

declare @Para1 int
declare @sql varchar(8000)

set @Para1=1
set @sql= case @Para1 when 1 then 'select * from T1 inner join ...' end

exec(@sql)

[huyouni]

luckyTOTO(NULL) ( ) 信誉：95 Blog 2007-03-16 16:23:55 得分: 0


不用动态语句


------------------------
那就用zlp321002(行走江湖，万事低调可保一路平安)的

[saiwong]

declare @sql varchar(4000)
select @sql='select * from T1'+case @Para1 when 1 then +' inner join T2 on ...' end +' where .....'
exec(@sql)

[paoluo]

似乎只有最笨的方法

Declare @Para1 Int
Select @Para1 = 1
If @Para1 = 1
select * from T1
inner join T2
On t1.id = t2.id
where t1.id > 0
Else
select * from T1
where t1.id > 0

[luckyTOTO]

zlp321002(行走江湖，万事低调可保一路平安) 谢谢 我想问一下 case 语句能用在from 子句中马？

[wangdehao]

那就直接用if语句吧

[luckyTOTO]

开来只能 这样了

[luckyTOTO]

可以在where 语句中变相解决：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GetSeriesCourseList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].udf_GetSeriesCourseList
GO

CREATE FUNCTION [dbo].udf_GetSeriesCourseList
(
@SeriesID int,
@IsIncludeInstructorLedCourse bit = 1,
@IsIncludeInactiveCourse bit = 0
)
/*
========================================================
Function: udf_GetSeriesCourseList
Description: Get the CourseUserStatus by courseuser or by courseuserround.
Parameters: @SeriesID -- id of series
@IsIncludeInstructorLedCourse -- 0,not in clude.1,include
@IsIncludeInactiveCourse -- 0,not include.1 include
Author: Walt Hu
Create Date: 03/15/2007
========================================================
Revision History
========================================================
Rev Date Author Description
========================================================
1 03/15/2007 Walt Hu Created
2 04/16/2007 Walt Hu Add parameter @IsIncludeInstructorLedCourse
3 04/16/2007 Walt Hu Exclude inactivecourse
========================================================
*/
RETURNS TABLE

AS
RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY st.Sequence ASC, ss.Sequence ASC ,c.Sequence ASC) AS RowNumber,
c.CourseID,
ss.SkillsetID,
st.SuiteID,
s.SeriesID
FROM
Course c
LEFT OUTER JOIN Skillset ss ON c.SkillsetID = ss.SkillsetID
LEFT OUTER JOIN Suite st ON ss.SuiteID = st.SuiteID
LEFT OUTER JOIN Series s ON st.SeriesID = s.SeriesID
LEFT OUTER JOIN CourseUser cu ON c.CourseID = cu.CourseID
WHERE st.SeriesID = @SeriesID
AND c.CourseTypeID = CASE @IsIncludeInstructorLedCourse WHEN 1 THEN c.CourseTypeID
ELSE 0
END
AND c.CourseStatusID = CASE @IsIncludeInactiveCourse WHEN 0 THEN 0
ELSE 1
END
AND ISNULL(cu.CourseUserID,0) = CASE @IsIncludeInactiveCourse WHEN 0 THEN 1
ELSE 0
END
)

[luckyTOTO]

错了： SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GetSeriesCourseList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].udf_GetSeriesCourseList
GO

CREATE FUNCTION [dbo].udf_GetSeriesCourseList
(
@SeriesID int,
@IsIncludeInstructorLedCourse bit = 1,
@IsIncludeInactiveCourse bit = 0,
@IsFilterByCourseUser bit = 1
)
/*
========================================================
Function: udf_GetSeriesCourseList
Description: Get the CourseUserStatus by courseuser or by courseuserround.
Parameters: @SeriesID -- id of series
@IsIncludeInstructorLedCourse -- 0,not in clude.1,include
@IsIncludeInactiveCourse -- 0,not include.1 include
@IsFilterByCourseUser -- 0,not filter by courseuser.1 filte by courseuser
Author: Walt Hu
Create Date: 03/15/2007
========================================================
Revision History
========================================================
Rev Date Author Description
========================================================
1 03/15/2007 Walt Hu Created
2 04/16/2007 Walt Hu Add parameter @IsIncludeInstructorLedCourse
3 04/16/2007 Walt Hu Exclude inactivecourse
========================================================
*/
RETURNS TABLE

AS
RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY st.Sequence ASC, ss.Sequence ASC ,c.Sequence ASC) AS RowNumber,
c.CourseID,
ss.SkillsetID,
st.SuiteID,
s.SeriesID
FROM
Course c
LEFT OUTER JOIN Skillset ss ON c.SkillsetID = ss.SkillsetID
LEFT OUTER JOIN Suite st ON ss.SuiteID = st.SuiteID
LEFT OUTER JOIN Series s ON st.SeriesID = s.SeriesID
LEFT OUTER JOIN CourseUser cu ON c.CourseID = cu.CourseID
WHERE st.SeriesID = @SeriesID
AND c.CourseTypeID = CASE @IsIncludeInstructorLedCourse WHEN 1 THEN c.CourseTypeID
ELSE 0
END
AND c.CourseStatusID = CASE @IsIncludeInactiveCourse WHEN 0 THEN 0
ELSE 1
END
AND ISNULL(cu.CourseUserID,0) = CASE @IsFilterByCourseUser WHEN 0 THEN 1
ELSE 0
END
)