on 2014 Mar 31 11:49 AM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
64 | |
8 | |
7 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.