Screen shots of the management application:
Here is an example stored procedure generated by the management application
USE [DataStagingDatabase]
GO
--/****** Object: StoredProcedure [dbo].[p_CrmInsert_Single_Contact] Script Date: 01/22/2009 06:58:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER Procedure [dbo].[p_CrmInsert_Single_Contact]
@SystemProfileName as Varchar(255),
@address1_addressid varchar (110),
@address1_addresstypecode varchar (255),
@address1_city varchar (50),
@address1_country varchar (50),
@address1_county varchar (50),
@address1_line1 varchar (50),
@address1_line2 varchar (50),
@address1_line3 varchar (50),
@address1_name varchar (200),
@address1_postalcode varchar (20),
@address1_telephone1 varchar (50),
@contactid varchar (110),
@emailaddress1 varchar (100),
@firstname varchar (50),
@gendercode varchar (255),
@lastname varchar (50)
AS
BEGIN
SET NOCOUNT ON
/*******************************************************************************************************/
/**** ****/
/**** Author: Autogenerated script by Fusion CRM CLR Application ****/
/**** Create Date: 1/22/2009 6:57:29 AM ****/
/**** Template: Stored procedure to insert a single record ****/
/**** ****/
/**** See http://www.fusionsoftware.co.uk/CrmClrInsert.htm for more information ****/
/**** ****/
/*******************************************************************************************************/
Declare @Result as int,
@MyXml xml,
@SystemProfileId as int
/* Get the SystemProfile ID */
Set @SystemProfileId = dbo.f_GetSystemProfileIdFromProfileName(@SystemProfileName)
/* Declare table variable to hold the data we want to insert */
Declare @contact_Inserts as table
(
[address1_addressid] [varchar] (110) Null,
[address1_addresstypecode] [varchar] (255) Null,
[address1_city] [varchar] (50) Null,
[address1_country] [varchar] (50) Null,
[address1_county] [varchar] (50) Null,
[address1_line1] [varchar] (50) Null,
[address1_line2] [varchar] (50) Null,
[address1_line3] [varchar] (50) Null,
[address1_name] [varchar] (200) Null,
[address1_postalcode] [varchar] (20) Null,
[address1_telephone1] [varchar] (50) Null,
[contactid] [varchar] (110) Null,
[emailaddress1] [varchar] (100) Null,
[firstname] [varchar] (50) Null,
[gendercode] [varchar] (255) Null,
[lastname] [varchar] (50) Null
)
/* Declare table variable to hold the data that is returned from the Procedure (Not required) */
Declare @InsertResults as table
(
[address1_addressid] [varchar] (110) Null,
[address1_addresstypecode] [varchar] (255) Null,
[address1_city] [varchar] (50) Null,
[address1_country] [varchar] (50) Null,
[address1_county] [varchar] (50) Null,
[address1_line1] [varchar] (50) Null,
[address1_line2] [varchar] (50) Null,
[address1_line3] [varchar] (50) Null,
[address1_name] [varchar] (200) Null,
[address1_postalcode] [varchar] (20) Null,
[address1_telephone1] [varchar] (50) Null,
[contactid] [varchar] (110) Null,
[emailaddress1] [varchar] (100) Null,
[firstname] [varchar] (50) Null,
[gendercode] [varchar] (255) Null,
[lastname] [varchar] (50) Null,
[Errors] [varchar](max), /* Include this column as the CrmClr stored procedures append these columns to the output */
[Warnings] [varchar](max) /* Include this column as the CrmClr stored procedures append these columns to the output */
)
/* Populate table variable with data we want to insert into CRM (one or more rows)*/
Insert Into @contact_Inserts
([address1_addressid], [address1_addresstypecode], [address1_city], [address1_country], [address1_county], [address1_line1], [address1_line2], [address1_line3], [address1_name], [address1_postalcode], [address1_telephone1], [contactid], [emailaddress1], [firstname], [gendercode], [lastname])
Values (
@address1_addressid, @address1_addresstypecode, @address1_city, @address1_country, @address1_county, @address1_line1, @address1_line2, @address1_line3, @address1_name, @address1_postalcode, @address1_telephone1, @contactid, @emailaddress1, @firstname, @gendercode, @lastname
)
/* Create the xml for inserting into CRM */
Set @MyXml =
(
Select [address1_addressid], [address1_addresstypecode], [address1_city], [address1_country], [address1_county], [address1_line1], [address1_line2], [address1_line3], [address1_name], [address1_postalcode], [address1_telephone1], [contactid], [emailaddress1], [firstname], [gendercode], [lastname]
From @contact_Inserts as contact /* Alias should be the CRM entity schema name */
For XML AUTO, ELEMENTS XSINIL /* Use this clause to generate Xml in the required format (and can accept Null values) */
)
/* Call the stored procedure to insert the data in CRM and insert results into temporary table */
Insert into @InsertResults
Exec @Result = dbo.p_CrmClr_Insert @MyXml, @SystemProfileId
/* Print out the result as text */
Print dbo.f_GetReturnText(@Result)
/* Select the results */
Select *
From @InsertResults
/* Example use of the stored procedure
Exec p_CrmInsert_Single_Contact
@SystemProfileName = 'Default',
@address1_addressid = '{acdeb971-cf3a-4a44-9ca4-cac1500087a0}',
@address1_addresstypecode = 'Other',
@address1_city = 'Insert address1_cityText',
@address1_country = 'Insert address1_countryText',
@address1_county = 'Insert address1_countyText',
@address1_line1 = 'Insert address1_line1Text',
@address1_line2 = 'Insert address1_line2Text',
@address1_line3 = 'Insert address1_line3Text',
@address1_name = 'Insert address1_nameText',
@address1_postalcode = 'address1_postalcode',
@address1_telephone1 = 'Insert address1_telephone1Text',
@contactid = '{dc8fb3c1-066f-4c09-9484-7d6d00cd0fb2}',
@emailaddress1 = 'Insert emailaddress1Text',
@firstname = 'Insert firstnameText',
@gendercode = 'Female',
@lastname = 'Insert lastnameText'
*/
Return @Result
End
|