Table Variables as Stored Procedure Parameters

Alex Deem

Hi, Is it possible to use table variables as parameters to a stored procedure? What i would like to do is: CREATE PROCEDURE [dbo].[spSetBillingPlan_Custom] @ContractNumber integer, @ArgTable TABLE(phasenum integer, perc decimal(5,4)) AS --yadda yadda yadda But it doesnt seem to like the table arguement. If it is not possible, can anyone offer an alternative solution to passing a variable amount of data to a stored proceedure? Thanks and Regards, Alex

Richard Deeming

This code came from one of the SQL Server sites:

CREATE FUNCTION fn_Split
(
@sText varchar(8000),
@sDelim varchar(20) = ' '
)
RETURNS @retArray TABLE
(
idx smallint Primary Key,
value varchar(8000)
)
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

SELECT
    @idx = 0,
    @bContinue = 1,
    @sText = LTrim(RTrim(@sText)),
    @iDelimLength = CASE
        WHEN @sDelim Is Null THEN 0
        WHEN @sDelim = 'Empty' THEN 0
        WHEN @sDelim = 'Space' THEN 1
        ELSE DataLength(@sDelim)
    END,
    @sDelim = CASE
        WHEN @sDelim Is Null THEN ''
        WHEN @sDelim = 'Empty' THEN ''
        WHEN @sDelim = 'Space' THEN ' '
        ELSE @sDelim
    END


IF NOT (@iDelimlength = 0)
BEGIN
    WHILE @bcontinue = 1
    BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

        IF CHARINDEX(@sDelim, @sText)>0
        BEGIN
            SET @value = LTrim(RTrim(SUBSTRING(@sText,1,CHARINDEX(@sDelim,@sText)-1)))

            INSERT INTO @retArray (idx, value)
            VALUES (@idx, @value)

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SELECT
@idx = @idx + 1,
@iStrike = DATALENGTH(@value) + @iDelimlength,
@sText = LTrim(RTrim(Right(@sText,DATALENGTH(@sText) - @iStrike)))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = LTrim(RTrim(@sText))
INSERT INTO @retArray (idx, value)
VALUES (@idx, @value)
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
INSERT INTO @retArray (idx, value)
VAL

Morten Abrahamsen

As you have correctly pointed out. SQL Server 2000 doesn't allow table variables as input parameters in stored procedures. What you could use instead is the "politically correct" xml document. Pass an xml document (array... ) to the SQL Server, and use the OPENXML statement to process it. :) Morty