存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER        proc [dbo].[proc_house_insert]
@house_ID varchar (10)=null,
@house_companyName varchar (50),
@huose_typeID varchar (10),
@house_seatID varchar (10),
@house_state varchar (10),
@house_fitmentID varchar (10),
@house_favorID varchar (10),
@house_mothedID varchar (10),
@huose_map varchar (50),
@house_price float, 
@house_floorID varchar (10),
@house_buildYear int, 
@house_area varchar (20),
@house_remark varchar (50),
@user_ids varchar(10)
as
begin
set @house_ID=(select Max(house_ID) from tb_house)
declare @sql varchar(300)
 if(@house_ID is null)
  set @house_ID='hou1001'
 else
  set @house_ID='hou'+cast(substring(@house_ID,4,4)+1 as varchar(10))
    
  insert into tb_house values
    (@house_ID,
    @house_companyName,
    @huose_typeID,
    @house_seatID,
    @house_state,
    @house_fitmentID,
    @house_favorID,
    @house_mothedID,
    @huose_map,
    @house_price,
    @house_floorID,
    @house_buildYear,
    @house_area,
    @house_remark,
    @user_ids )

--上面先插入
  set @sql='select user_id 用户编号,house_price 房价,house_area 房屋面积 from tb_intent where
    huose_typeID ='''+@huose_typeID+'''
    and house_seatID='''+@house_seatID+'''
    and house_fitmentID='''+@house_fitmentID+'''
    and house_floorID='''+@house_floorID+'''
    and house_favorID='''+@house_favorID+'''
    and house_mothedID='''+@house_mothedID+'''
   '
  print @sql
  exec (@sql)
end