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

asked 31 Mar '14, 10:49

Frank's gravatar image

Frank
1205613
accept rate: 0%

edited 31 Mar '14, 10:53

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275

1

Wouldn't it be more appropriate to declare that as a function in SQL Anywhere, too - you only use IN parameters and get a return value...

BTW: What SQL Anywhere version do you use?

(31 Mar '14, 10:54) Volker Barth

SQL Anywhere version 12.0.1 build 3976.

I can't get the procedure this way working in Sybase with proxy tables to the SQL server database because the exists() produces an error. The error I receive from SQL Server is:

Connection is busy with results for another hstmt

(31 Mar '14, 11:25) Frank
Replies hidden
1

Sorry, my suggestion was not to port the SQL code to SQL Anywhere (via using proxy tables) but to declare the external procedure as an external function instead, such as:

CREATE FUNCTION usr.TF2_DetermineNetworkAreaId(
  in_transportTypeId int, in_CountryCode varchar(3), in_ZipCode varchar(10))
  RETURNS int
  AT 'Quote2;quote;dbo;DetermineNetworkAreaId';
(31 Mar '14, 11:31) Volker Barth

Oki, now I get it. somehow hasn't come up in me to look at that.

Thanks for the input

(31 Mar '14, 11:35) Frank

The stored procedure was created with the wizard and that always create a stored procedure instead of a function. That's probably why I didn't notice this difference.

Thanks again

(01 Apr '14, 02:51) Frank

Well, now it's not clear if the real problem is solved - when you use a stored function, does the desired result show up?

(01 Apr '14, 03:50) Volker Barth
showing 3 of 6 show all flat view

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.

permanent link

answered 01 Apr '14, 04:26

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 01 Apr '14, 04:30

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×48
×19

question asked: 31 Mar '14, 10:49

question was seen: 1,911 times

last updated: 01 Apr '14, 04:30