dbo.DA_GetLayerModel.StoredProcedure.sql
3.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[DA_GetLayerModel] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: magic
-- Create date: 17/4/2009
-- Description: Fetch Layer Data for given body view id and gender
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DA_GetLayerModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DA_GetLayerModel]
GO
CREATE PROCEDURE [dbo].[DA_GetLayerModel]
-- Add the parameters for the stored procedure here
@body_view_id int ,
@sex char
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT dl.Id , dl.LayerNumber , dl.LayerNumberInternal , dl.LayerName ,
ISNULL(dl.IsSkin, 'N') AS IsSkin ,
ISNULL(dl.IsBase, 'N') AS IsBase , map.DissectibleRegionId ,
map.LayerNumberInternal , art.Zoom ,
ISNULL(art1.ImageId, 0) AS ISOID ,
art.ImageId AS COMPID
FROM DissectibleRegionLayerArt art INNER JOIN
DissectibleRegionLayerMapping map ON art.DissectibleRegionLayerMappingId = map.Id INNER JOIN
DissectibleLayer dl ON map.DissectibleLayerId = dl.Id LEFT OUTER JOIN
DissectibleRegionLayerArt art1 ON map.Id = art1.DissectibleRegionLayerMappingId AND art1.IsIsolate = 'Y' AND art1.IsBetadine <> 'Y' AND
art1.IsDermatome <> 'Y' AND art1.IsBald <> 'Y' AND art1.IsHair <> 'Y'
WHERE (map.BodyViewId = @body_view_id) AND (ISNULL(map.Sex, '+') = @sex) AND (art.IsBetadine <> 'Y') AND (art.IsDermatome <> 'Y') AND (art.IsBald <> 'Y') AND
(art.IsHair <> 'Y') AND (art.IsIsolate = 'N')
ORDER BY dl.Id DESC, dl.LayerNumber DESC, map.DissectibleRegionId DESC
END
GO