Re: SQL StoredProc Help

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Boss Hog (replace)
Date: 08/03/04 

Date: Tue, 3 Aug 2004 10:35:21 +0200

Hi,
if i have understand you need to pass a xml stream to your stored proc ?!
in this exemple i send a xml ID list to storedProc, this is a list of the
products user right :

I hope to help you.
@+
Boss Hog
--Call the stored proc
exec CPP_PS_GET_SURVEY_BY_USERID 0,0,'<?xml version="1.0"?><root><row
I="1"/><row I="2"/><row I="5"/><row I="6"/></root>'

--Exemple of Stored Proc
Create Procedure CPP_PS_GET_SURVEY_BY_USERID
(
@USER_ID int,
@FORXML int = 0,
@CORP_PRODUCT_ID_LIST ntext
)
as
Begin
Declare @CORP_PRODUCT_COUNT int
DECLARE @COUNT_READER int
DECLARE @COUNT_CONTRIBUTING int
set @CORP_PRODUCT_COUNT = 0
declare @table table(CORP_PRODUCT_ID int)
declare @idoc int

 If substring(@CORP_PRODUCT_ID_LIST,0,10)<> ''
  Begin
   --Load XML dataset
   EXEC sp_xml_preparedocument @idoc OUTPUT, @CORP_PRODUCT_ID_LIST
   insert into @table
   select distinct RFP.CORP_PRODUCT_ID from CPP_RF_PRODUCT RFP
   where RFP.CORP_PRODUCT_ID in (SELECT * FROM OPENXML (@idoc,
'/root/row',1)WITH (I int))
   Exec sp_xml_removedocument @idoc
  End
 End
End

"Boomessh" <Boomessh@discussions.microsoft.com> wrote in message
news:3DA6B8F4-C5A9-4B0F-9591-4765E5F7BA4E@microsoft.com...
> Hai All,
>
> I need an Help on Stored Proc.
> can i pass an XML / DataTable as a input parameter to an SQL Stored Proc.
>
> From the UI, I get the input from user as Datatable.
> Structure of the Datatable is 4 cols (say ID, Name, age, address)
> now can i pass this(Datatable/xml) as an input param to the stored proc.
>
> If not any other way by which i can do this?
>
> Thanks,
> V.Boomessh

Relevant Pages

  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... I got an xml routine from the Sql Server NG where I can ... delimiters in the string. ... Declare @iDoc Int ...
    (microsoft.public.sqlserver.xml)
  • Re: SQL StoredProc Help
    ... Hai Boss Hog, ... The XML i use is like this.. ... DECLARE @IDOC INT ...
    (microsoft.public.sqlserver.xml)
  • Re: Calling Stored Proc from a Function
    ... DECLARE @rc AS INT, @seqid AS INT; ... UPDATE sequences ... > need to do an update statement, I have to use a stored proc as you can ...
    (microsoft.public.sqlserver.programming)
  • Re: Convert NTEXT to XML for use in following query.
    ... The XML DataType supports instances up to two 2gb so that seems unlikely. ... It works for me for lists from 100 to 1000000 elements. ... create table dbo.test(id int identity(1,1) primary key clustered,v int) ... declare @list xml ...
    (microsoft.public.sqlserver.xml)