Aug 112006
 

A request came to me asking how to create a stored procedure call with no arguments.

Here is the steps for the following example:

Run the following script against the database:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[example]’) and OBJECTPROPERTY(id, rx N’IsProcedure’) = 1)
drop procedure [dbo].[example]
GO

CREATE PROCEDURE dbo.example
AS
BEGIN
 SELECT ’Hello’ [Result]
 FOR XML RAW, medical xmldata
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Create the new BizTalk Project

Generate the stored procedure schema (make sure that you add xmldata for example ‘for xml raw, xmldata’)

Right click and generate the xml document, it should look like this:
xmlMsg.JPG

In a text editor remove the CRLF and replace the quotes with ‘ ” ‘

Create a new variable (I called it tempXML) and choose a .NET class and choose XML Document
XmlDocumentType.JPG

In your message assigment, use the following code:
messageAssignment.JPG

That is it, you have completed the creation of a blank stored procedure call.