存储过程

USE [zlhomeV3.0]
GO
/****** Object:  StoredProcedure [dbo].[SpChangeNewHouseAgency]    Script Date: 06/21/2012 15:54:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SpChangeNewHouseAgency]
@Options VarChar(100),
@Id bigint=Null Output,
@Name nvarchar(50)=Null,
@NameAbbreviate nvarchar(50)=Null,
@CategoriesId bigint=Null,
@CategoriesName nvarchar(50)=Null,
@SareaCategoriesId bigint=Null,
@SareaDistrictCategoriesId bigint=Null,
@SareaDistrictCategoriesName nvarchar(50)=Null,
@SareaCategoriesName nvarchar(50)=Null,
@HouseTrait nvarchar(100)=Null,
@NewHousePrice money=Null,
@SellDate datetime=Null,
@SellTel nvarchar(50)=Null,
@BuildCompany nvarchar(50)=Null,
@InvestCompany nvarchar(50)=Null,
@Address nvarchar(100)=Null,
@TenementCategoriesID nvarchar(100)=Null,
@TenementCategoriesName nvarchar(100)=Null,
@FitmentSituation nvarchar(50)=Null,
@PropertyRightDate datetime=Null,
@PlanArea float=0,
@BuildArea float=0,
@PlanOCC bigint=Null,
@IndoorParkingSpaceNumber int=Null,
@OutdoorParkingSpaceNumber int=Null,
@IndoorParkingSpacePrice money=Null,
@OutdoorParkingSpacePrice money=Null,
@FAR float=0,
@VirescencePe float,
@ManageCompany nvarchar(50)=Null,
@ManagePrice money=Null,
@Description ntext=Null,
@AreaDescription nvarchar(200)=Null,
@TrafficAbout nvarchar(200)=Null,
@TrafficBiz nvarchar(200)=Null,
@Freeway nvarchar(200)=Null,
@TeachSchool nvarchar(200)=Null,
@InfrastructureHospital nvarchar(200)=Null,
@InfrastructureBank nvarchar(200)=Null,
@InfrastructureElse nvarchar(200)=Null,
@NewHouseAgencyAssort nvarchar(200)=Null,
@Advantage nvarchar(500)=Null,
@Shortcoming nvarchar(500)=Null,
@KeyWord nvarchar(1000)=Null,
@ImgId uniqueidentifier=Null,
@IX float=0,
@IY float=0,
@CreateDate datetime=Null,
@StateCode smallint=Null,
@IdList VarChar(max)=Null,
@ImgIdList VarChar(max)=Null
 AS
Declare @Sql VarChar(max)
Declare @Sql1 VarChar(max)
If @Options='Insert' Begin
--判断是否有重复楼盘资料
If Exists(Select Id From NewHouseAgency Where Name=RTrim(LTrim(@Name)) And SareaCategoriesId=@SareaCategoriesId) Return 2
BEGIN TRANSACTION
BEGIN TRY
--判断选择的默认图片是否在上传图片ID集合中 不存在则设置下一图片为默认
If Len(@ImgIdList)>0 Begin
if(Charindex(Cast(@ImgId as NVarChar(100)),@ImgIdList)<1) Begin
Set @ImgId=(Select Top 1 Value From dbo.Split(replace(@ImgIdList,'''',''),','))
End
End Else Begin
Set @ImgId='00000000-0000-0000-0000-000000000000'
End
Insert Into NewHouseAgency (
[Name] ,
[NameAbbreviate] ,
[CategoriesId] ,
[CategoriesName] ,
[SareaCategoriesId] ,
[SareaDistrictCategoriesId] ,
[SareaDistrictCategoriesName] ,
[SareaCategoriesName] ,
[HouseTrait] ,
[NewHousePrice] ,
[SellDate] ,
[SellTel] ,
[BuildCompany] ,
[InvestCompany] ,
[Address] ,
[TenementCategoriesID] ,
[TenementCategoriesName] ,
[FitmentSituation] ,
[PropertyRightDate] ,
[PlanArea] ,
[BuildArea] ,
[PlanOCC] ,
[IndoorParkingSpaceNumber] ,
[OutdoorParkingSpaceNumber] ,
[IndoorParkingSpacePrice] ,
[OutdoorParkingSpacePrice] ,
[FAR] ,
[VirescencePe] ,
[ManageCompany] ,
[ManagePrice] ,
[Description] ,
[AreaDescription] ,
[TrafficAbout] ,
[TrafficBiz] ,
[Freeway] ,
[TeachSchool] ,
[InfrastructureHospital] ,
[InfrastructureBank] ,
[InfrastructureElse] ,
[NewHouseAgencyAssort] ,
[Advantage] ,
[Shortcoming] ,
[KeyWord] ,
[ImgId] ,
[IX] ,
[IY] ,
[CreateDate] ,
[StateCode]
)
Select
@Name,
@NameAbbreviate,
@CategoriesId,
ISNULL((Select Top 1 CategoriesName From dbo.CommunityCategories CC Where CC.CategoriesId=@CategoriesId),''),
@SareaCategoriesId,
IsNull(L2.CategoriesId,0),
IsNull(L2.CategoriesName,''),
IsNull(L1.CategoriesName,''),
@HouseTrait,
@NewHousePrice,
@SellDate,
@SellTel,
@BuildCompany,
@InvestCompany,
@Address,
ISNULL((Select Top 1 CategoriesName From dbo.NewHouseTeneCategories CC Where CC.CategoriesId=@CategoriesId),''),
@TenementCategoriesName,
@FitmentSituation,
@PropertyRightDate,
@PlanArea,
@BuildArea,
@PlanOCC,
@IndoorParkingSpaceNumber,
@OutdoorParkingSpaceNumber,
@IndoorParkingSpacePrice,
@OutdoorParkingSpacePrice,
@FAR,
@VirescencePe,
@ManageCompany,
@ManagePrice,
@Description,
@AreaDescription,
@TrafficAbout,
@TrafficBiz,
@Freeway,
@TeachSchool,
@InfrastructureHospital,
@InfrastructureBank,
@InfrastructureElse,
@NewHouseAgencyAssort,
@Advantage,
@Shortcoming,
@Name+@NameAbbreviate+@Address,
@ImgId,
@IX,
@IY,
GetDate(),
0
From dbo.LocusCategories L1
Left outer join dbo.LocusCategories L2 on L1.PaternCategoriesId=L2.CategoriesId
Where L1.CategoriesId=@SareaCategoriesId
set @Id=SCOPE_IDENTITY()
/*
If Not @ImgIdList Is Null And @ImgIdList<>'' Begin
UpDate CI Set
Name=IDNL.Value,
NewHouseAgencyId=@Id,
RoomNumber=RDNL.Value,
ParlorNumber=PDNL.Value,
ToiletNumber=TDNL.Value
From NewHouseAgencyImgs CI
Inner Join dbo.Split(replace(@ImgIdList,'''',''),',') IDIL On IDIL.Value=CI.Id
Inner Join dbo.Split(@NameList,',') IDNL On IDIL.Id=IDNL.Id
Inner Join dbo.Split(@RoomNumberList,',') RDNL On RDNL.Id=IDNL.Id
Inner Join dbo.Split(@ParlorNumberList,',') PDNL On PDNL.Id=IDNL.Id
Inner Join dbo.Split(@ToiletNumberList,',') TDNL On TDNL.Id=IDNL.Id
Where Not IDIL.Value Is Null
End*/
COMMIT
End Try
BEGIN CATCH
ROLLBACK
Return 1
END CATCH
Return 0
End
If @Options='UpDate' Begin
BEGIN TRANSACTION
BEGIN TRY
--判断选择的默认图片是否在上传图片ID集合中 不存在则设置下一图片为默认
If Len(@ImgIdList)>0 Begin
if(Charindex(Cast(@ImgId as NVarChar(100)),@ImgIdList)<1) Begin
Set @ImgId=(Select Top 1 Value From dbo.Split(replace(@ImgIdList,'''',''),','))
End
End Else Begin
Set @ImgId='00000000-0000-0000-0000-000000000000'
End
UpDate C Set
[Name]=@Name ,
[NameAbbreviate]=@NameAbbreviate ,
[CategoriesId]=@CategoriesId ,
[CategoriesName]=@CategoriesName ,
[SareaCategoriesId]=@SareaCategoriesId ,
[SareaDistrictCategoriesId]=@SareaDistrictCategoriesId ,
[SareaDistrictCategoriesName]=@SareaDistrictCategoriesName ,
[SareaCategoriesName]=@SareaCategoriesName ,
[HouseTrait]=@HouseTrait ,
[NewHousePrice]=@NewHousePrice ,
[SellDate]=@SellDate ,
[SellTel]=@SellTel ,
[BuildCompany]=@BuildCompany ,
[InvestCompany]=@InvestCompany ,
[Address]=@Address ,
[TenementCategoriesID]=@TenementCategoriesID ,
[TenementCategoriesName]=@TenementCategoriesName ,
[FitmentSituation]=@FitmentSituation ,
[PropertyRightDate]=@PropertyRightDate ,
[PlanArea]=@PlanArea ,
[BuildArea]=@BuildArea ,
[PlanOCC]=@PlanOCC ,
[IndoorParkingSpaceNumber]=@IndoorParkingSpaceNumber ,
[OutdoorParkingSpaceNumber]=@OutdoorParkingSpaceNumber ,
[IndoorParkingSpacePrice]=@IndoorParkingSpacePrice ,
[OutdoorParkingSpacePrice]=@OutdoorParkingSpacePrice ,
[FAR]=@FAR ,
[VirescencePe]=@VirescencePe ,
[ManageCompany]=@ManageCompany ,
[ManagePrice]=@ManagePrice ,
[Description]=@Description ,
[AreaDescription]=@AreaDescription ,
[TrafficAbout]=@TrafficAbout ,
[TrafficBiz]=@TrafficBiz ,
[Freeway]=@Freeway ,
[TeachSchool]=@TeachSchool ,
[InfrastructureHospital]=@InfrastructureHospital ,
[InfrastructureBank]=@InfrastructureBank ,
[InfrastructureElse]=@InfrastructureElse ,
[NewHouseAgencyAssort]=@NewHouseAgencyAssort ,
[Advantage]=@Advantage ,
[Shortcoming]=@Shortcoming ,
[KeyWord]=@KeyWord ,
[ImgId]=@ImgId ,
[IX]=@IX ,
[IY]=@IY ,
[CreateDate]=@CreateDate ,
[StateCode]=@StateCode
From dbo.NewHouseAgency C
Left outer join dbo.LocusCategories L1 on L1.CategoriesId=@SareaCategoriesId
Left outer join dbo.LocusCategories L2 on L1.PaternCategoriesId=L2.CategoriesId
Where Id=@Id
Set @Sql='Delete NewHouseAgencyImgs Where NewHouseAgencyId='''+Cast(@Id as VarChar(100))+''' '
If len(@ImgIdList)>0 Begin
Set @Sql=@Sql+' And Not Id In ('+@ImgIdList+')'
End
Exec(@Sql)
/*
If Not @ImgIdList Is Null And @ImgIdList<>'' Begin
UpDate CI Set
Name=IDNL.Value,
NewHouseAgencyId=@Id,
RoomNumber=RDNL.Value,
ParlorNumber=PDNL.Value,
ToiletNumber=TDNL.Value
From NewHouseAgencyImgs CI
Inner Join dbo.Split(replace(@ImgIdList,'''',''),',') IDIL On IDIL.Value=CI.Id
Inner Join dbo.Split(@NameList,',') IDNL On IDIL.Id=IDNL.Id
Inner Join dbo.Split(@RoomNumberList,',') RDNL On RDNL.Id=IDNL.Id
Inner Join dbo.Split(@ParlorNumberList,',') PDNL On PDNL.Id=IDNL.Id
Inner Join dbo.Split(@ToiletNumberList,',') TDNL On TDNL.Id=IDNL.Id
Where Not IDIL.Value Is Null
End
*/
COMMIT
End Try
BEGIN CATCH
ROLLBACK
Return 1
END CATCH
Return 0
End
If @Options='Delete' Begin
BEGIN TRANSACTION
BEGIN TRY
--删除楼盘图库表
Delete NewHouseAgencyImgs Where NewHouseAgencyId in (Select Value From dbo.Split(@IdList,','))
--删除楼盘主表
Delete NewHouseAgency Where Id in (Select Value From dbo.Split(@IdList,','))
COMMIT
End Try
BEGIN CATCH
ROLLBACK
Return 1
END CATCH
Return 0
End
GO
=========================================
select case when c.colid=1 then 
object_name(c.id) else '' end as 表名,c.name   as   字段名 ,t.name   数据类型  ,c.prec   as   长度  ,p.value  as   描述信息  ,
m.text   as   默认值  from  syscolumns c  inner join systypes t on c.xusertype=t.xusertype  
left join sys.extended_properties p on c.id=p.major_id and c.colid = p.minor_id  left join syscomments m on c.cdefault=m.id  
where objectproperty(c.id,'IsUserTable')=1  and object_name(c.id)='ClientOS' 
select * from sys.extended_properties
select id,colid from syscolumns
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sys.extended_properties ON
   ( sys.extended_properties.minor_id = syscolumns.colid
     AND sys.extended_properties.major_id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN 
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname') and Sysobjects.name='Client'

    ORDER BY syscolumns.colid  

posted @ 2012-07-09 09:59  小开的一天  阅读(9708)  评论(0)    收藏  举报