dbo.DA_GetLayerdata.StoredProcedure.sql
3.78 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_GetLayerdata] 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 List
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DA_GetLayerdata]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DA_GetLayerdata]
GO
CREATE PROCEDURE [dbo].[DA_GetLayerdata]
-- 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(
SELECT dl.Id AS id, dl.LayerNumber AS ln, dl.LayerNumberInternal AS lni, dl.LayerName AS lnm,
ISNULL(dl.IsSkin, 'N') AS issk,
ISNULL(dl.IsBase, 'N') AS isbs, map.DissectibleRegionId AS brId,
map.LayerNumberInternal AS lni, art.SkinTone AS sktn, art.Zoom AS zm,
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 FOR XML AUTO)
as XMLString
END
GO