SQL Server Function Error

[Brian1960]

Hi, I have an Access front end with a SQL Server backend and need to able to call a User define function in a query. To do this I need to create the function but I can't get the right syntax.

CREATE FUNCTION BCL_GetForeignKeyCode(@param1 as varchar(16))  
RETURNS Integer
AS  
BEGIN 
	declare @intPos Integer;
	declare @sTempValue varchar(16);
	declare @sNumber Integer;
	declare @iRet Integer;
	declare @iRett Integer;
	declare @sRet varchar(16);
	declare @sStr varchar(16);

	set  @iRet = 1;
	set @iRett = 1;

	While @sStr <>"O"
	Begin
		 @sStr=SubString(@param1,@iRet,1)
		 @sTempValue=@sTempValue +  @sStr
		 @iRet=@iRet + 1
	End
            'Convert from Hex to double ??? How
	Set @sNumber = "&H'" & @sTempValue;
       	 
	While @iRett <= Len(@sNumber)
	Begin
                	@sRet=SubString(@sNumber, @iRet, 1) + @sRet
		@iRett=@iRett + 1
	End

Return @sRet
END

The error (170) is in two places
Line 18 near @sStr
Line 27 near @sRet

The first part takes a string and I want it to just collect all the characters up until the letter O.
The second bit needs to covert what is now a Hexadecimal number to an actual number and the 3rd and final part reverses the digits.
Okay a decryption!! I need to use in a query hence the UDF.
Thanks

 

[MSAccessRookie]

Hi, I have an Access front end with a SQL Server backend and need to able to call a User define function in a query. To do this I need to create the function but I can't get the right syntax.

CREATE FUNCTION BCL_GetForeignKeyCode(@param1 as varchar(16))  
RETURNS Integer
AS  
BEGIN 
    declare @intPos Integer;
    declare @sTempValue varchar(16);
    declare @sNumber Integer;
    declare @iRet Integer;
    declare @iRett Integer;
    declare @sRet varchar(16);
    declare @sStr varchar(16);
 
    set  @iRet = 1;
    set @iRett = 1;
    [COLOR=red][B]set @sRet = "";[/B][/COLOR]
 
    While @sStr <>"O"
    Begin
         @sStr=SubString(@param1,@iRet,1)
         @sTempValue=@sTempValue +  @sStr
         @iRet=@iRet + 1
    End
            'Convert from Hex to double ??? How
    Set @sNumber = "&H'" & @sTempValue;
 
    While @iRett <= Len(@sNumber)
    Begin
                    @sRet=SubString([COLOR=darkorchid][B]@sNumber[/B][/COLOR], @iRet, 1) + @sRet
        @iRett=@iRett + 1
    End
 
Return @sRet
END

The error (170) is in two places
Line 18 near @sStr
Line 27 near @sRet

The first part takes a string and I want it to just collect all the characters up until the letter O.
The second bit needs to covert what is now a Hexadecimal number to an actual number and the 3rd and final part reverses the digits.
Okay a decryption!! I need to use in a query hence the UDF.
Thanks

There are two things that stand out to me. First, you do not initialize the variable @sRet, and second, you are trying to take a substring from an Integer variable @sNumber. I am not sure that either of these would create your problem, but you should address both of them and see what happens.