August 16, 2011 at 5:10 pm
I got a xml file, i need to convert that file in to store procedure.
I am ok to convet this XML file in to store proc but only problem is the last line select last_insert_id() as vendor_cache_id
is not clear will any one explain clearly please..
<sqlMap namespace="vendorCache">
<typeAlias alias="vendorCacheData"
type="com.corelogic.ript.business.vendor.dbcache.VendorCacheData" />
<parameterMap id="vendorCacheDataParams" class="vendorCacheData" >
<parameter property="vendorCacheId" jdbcType="Integer" />
<parameter property="vendorId" jdbcType="Integer" />
<parameter property="customerId" jdbcType="VARCHAR" />
<parameter property="formattedQuery" jdbcType="VARCHAR" />
<parameter property="vendorData" jdbcType="BLOB" />
</parameterMap>
<resultMap id="vendorCacheDataResult" class="vendorCacheData">
<result property="vendorCacheId" column="vendor_cache_id" />
<result property="vendorId" column="vendor_id" />
<result property="customerId" column="customer_id" />
<result property="formattedQuery" column="formatted_query" />
<result property="vendorData" column="vendor_data" />
</resultMap>
<select id=" selectCacheData" resultMap="vendorCacheDataResult"
parameterClass="vendorCacheData">
select * from vendor_cache
where vendor_id=#vendorId#
<isNull prepend="and" property="customerId">
customer_id is null
</isNull>
<isNotNull prepend="and" property="customerId">
customer_id=#customerId#
</isNotNull>
and formatted_query=#formattedQuery#
</select>
<insert id="insertCacheData" parameterClass="vendorCacheData">
insert into vendor_cache (vendor_id,customer_id,formatted_query,vendor_data)
values (#vendorId#,#customerId#,#formattedQuery#,#vendorData#)
<selectKey resultClass="int" keyProperty="vendorCacheId">
select last_insert_id() as vendor_cache_id
</selectKey>
</insert>
</sqlMap>
August 16, 2011 at 6:03 pm
I got the store procedure for above XML, The problem is i don't know how to insert vendor_cache_id it is underlined in XML file.
/****** Object: StoredProcedure [dbo].[usp_vendorCacheData] Script Date: 08/16/2011 04:14:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[usp_vendorCacheData]
@VendorCacheId INT,
@VendorID INT,
@customerId VARCHAR,
@formattedQuery VARCHAR
AS
Begin
SET NOCOUNT ON
DECLARE @sql NVARCHAR(4000)
Set @sql= ' Select * from vendor_cache '
If @VendorID is NOt NULL
BEGIN
Set @sql= @sql+ ' (NOLOCK) where VendorID = @spVendorID'
END
IF @customerId is NOT NULL
BEGIN
Set @sql= @sql+ ' and customerId = @spcustomerId'
END
IF @formattedQuery is NOT NULL
BEGIN
SET @sql= @sql+ ' and formattedQuery = @spformattedQuery'
END
Exec sp_executesql @sql, N'@spVendorID INT, @spcustomerId VARCHAR, @spformattedQuery VARCHAR',
@VendorID = @spVendorID, @customerId = @spcustomerId , @formattedQuery = @spformattedQuery
END
/************************* Object Store procedures: [dbo].[usp_InsertvendorCacheData] ***************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[usp_InsertvendorCacheData]
@VendorCacheId INT,
@VendorID INT,
@customerId VARCHAR,
@formattedQuery VARCHAR,
@vendorData nVARCHAR(MAX)
AS
BEGIN
INSERT INTO [dbo].[vendor_cache]
( vendorId
, customer_id
, formatted_query
, vendorData)
Select @VendorID
, @customerId
, @formattedQuery
, @vendorData
END
August 17, 2011 at 2:50 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply