SAP SQL Anywhere

Question

How can I pass parameters to a remote MS SQL Server procedure? I can't seem to get it work.

Probably missing something, but I can't find what.

Remote procedure in Sybase:

CREATE PROCEDURE usr.TF2_DetermineNetworkAreaId(in in_transportTypeId int,in in_CountryCode varchar(3),in in_ZipCode varchar(10)) at 'Quote2;quote;dbo;DetermineNetworkAreaId';

Procedure in SQL Server:

    CREATE FUNCTION dbo.DetermineNetworkAreaId
    (   @TransportTypeId int,
        @CountryCode nvarchar(3),
        @ZipCode nvarchar(10))
    RETURNS int
    AS
    BEGIN
      declare @RegionId int
      --
      set @RegionId = (select 
                          top 1 RegionId
                       from 
                          dbo.Region join
                          dbo.RegionAreas on RegionAreas.RegionId = Region.Id join
                          dbo.Country on Country.Id = RegionAreas.CountryId
                       where 
                          Region.TransportTypeId = @TransportTypeId and
                          (RegionAreas.Exclude = 0 and ((Country.Code = @CountryCode and RegionAreas.ZipCode = substring(@ZipCode,0,LEN(RegionAreas.ZipCode))) or
                                                        (Country.Code = @CountryCode and RegionAreas.ZipCode = ''))
                          ) and 
                          not exists(select 1 
                                     from dbo.RegionAreas RA join
                                        dbo.Country C on RA.countryId = C.Id
                                     where RegionId = RA.RegionId and 
                                           Exclude = 1 and ((C.Code = @CountryCode and RA.ZipCode = substring(@ZipCode,0,LEN(RA.ZipCode))) or
                                                            (C.Code = @CountryCode and RA.ZipCode = ''))
                                     )
                        order by Country.Code desc, RegionAreas.ZipCode desc, RegionId desc)
      --
      return @RegionId                    
    END
    GO

When calling "select "USR"."TF2_DetermineNetworkAreaId"(204,'NLD','')" in I-SQL I get a result 0
When calling the same procedure in SQL Server the result is 40

Regards,

Frank

Answer 1

Here's a simple sample with a MS SQL server function that just returns the column length of a specified column of a specified table (without any error handling...) - tested with MS SQL 2008 R2:

create function dbo.FCTN_SysColumnLength(@table_name sysname, @colid int) returns int
as
begin
  declare @length int
  set @length =
    (select length from syscolumns SC inner join sysobjects SO on SC.id = SO.id
        where SO.name = @table_name and SC.colid = @colid)
  return @length
end

Calling that function with the default table sysfiles1 for its 4th column returns 520:

select dbo.FCTN_SysColumnLength('sysfiles1', 4)

In SQL Anywhere, I declared a server class MSSODBC (with the appropriate DSN and credentials) and simply added a stored function that relates to the MSS function:

create server MSS_Test class 'MSSODBC' using 'DSN=...';

create function FCTN_SysColumnLength(@table_name sysname, @colid int) returns int
   at 'MSS_Test...FCTN_SysColumnLength';

select FCTN_SysColumnLength('sysfiles1', 4) -- returns 520 as well

Note: When I do use a stored procedure within SQL Anyhwere instead, this does not work (and seems to meet your test results):

-- Note: Parameters must be declared as "in" as inout would be the default
create procedure STP_SysColumnLength(in @table_name sysname, in @colid int)
at 'MSS_Test...FCTN_SysColumnLength';
-- use a variable to accept and display the procedure's return value
begin
   declare nLength int;
   nLength = call STP_SysColumnLength('sysfiles1', 4);
   select nLength;
end;

This returns 0 instead of 520.

Conclusion: It seems necessary - and logically appropriate - to use a stored function both within MS SQL Server and SQL Anywhere.

SAP SQL Anywhere

How to pass parameters to a remote procedure from Sybase to MS SQL Server

Follow this question

Related questions