CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlpowerbuilder
DARKinVADER
DARKinVADER

Reputation: 640

How to declare Stored procedure with Table-valued parameter in PowerBuilder

I made a TVP stored procedure in MS SQL Server 2008:

CREATE PROCEDURE [TVPSP] @CID INT
    ,@OID INT = NULL
    ,@GAS ParamTypeTVP readonly
    ,@ErrorText VARCHAR(100) OUTPUT
AS
BEGIN
    select 1

    RETURN
END

I have also created a TVP Type:

CREATE TYPE [MDF].[ParamTypeTVP] AS TABLE(
    [ParamID] [int] IDENTITY(1,1) NOT NULL,
    [pInt1] [int] NULL,
    [pVarchar1] [varchar](777) NULL,
    PRIMARY KEY CLUSTERED 
(
    [ParamID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

I generated the declaration with the Powerbuilder built-in method, the result is:

function long TVPSP(long CID,long OID,string GAS,ref string ErrorText) RPCFUNC ALIAS FOR "TVPSP"

So as you see the PB generated a string variable for the TVP parameter which is false (and of course the call fails from PB). Is it possible to make it work somehow? Of course I have ideas for workarounds (for example making a wrapper sp to ignore the TVP parameters), but I would like to know how to make it work in this way with TVP!

Thx in advance!

Gábor

Upvotes: 2

Views: 1603

Answers (1)

Rich Bianco
Rich Bianco

Reputation: 4174

Bad news, it may not be supported based on this from the PB12.5 documentation:

Unsupported SQL Server 2008 features

The PowerBuilder SNC interface does not support the User-Defined Table Type (a user-defined type that represents the definition of a table structure) that was introduced in SQL Server 2008.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.pb.12.5/title.htm

IF you wanted to keep trying (if there is a will there is a way) then try using data type of "any" instead of string, or try making a structure that matches the table and pass an array of your structure as parameter instead of the string.

Upvotes: 1

Related Questions