• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Applying SQL 2005 function on SQL 2000 machine.

I have a function that is designed for SQL 2005 but we have some SQL 2000 clients and they will get the same upgrade but the application of the function will fail. Is there a way to conditionally apply this function based on which SQL version they are on(2000 or 2005) ?

Here is the function that does apply on SQL 2005 but generates an error on SQL 2000.

The specific line that 2000 doesnt like is:
SET @as_base64 = cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))', 'varchar(20)')

I tried using dynamic sql such as IF CHARINDEX('SQL Server 2005',@@version,1) > 0
SET @ls_stmt = '  cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))', 'varchar(20)') '
which seems like it would work but still generated an error.






If Exists (	Select	1 
			From	sysobjects 
			Where	name = 'jds_sf_bin2base64' And  type = 'FN'
		   )
    Drop Function DBO.jds_sf_bin2base64

GO


CREATE function[dbo].[jds_sf_bin2base64]
  ( @as_binary_string varchar(254) ) 
returns varchar(20)
AS
BEGIN

	DECLARE
		@li_power integer,
		@li_bin_position integer,
		@ls_bin_part varchar(4),
		@li_dec integer,
		@ls_hex_string varchar(10),
		@lb_binaryhex varbinary(1000),
		@as_base64  varchar(20)
			
	SET @li_bin_position = LEN(@as_binary_string)
	SET @li_power = 0
	SET @li_dec = 0
	
	WHILE @li_bin_position > 0
	BEGIN
		SET @ls_bin_part = SUBSTRING(@as_binary_string, @li_bin_position, 1)
		SET @li_dec = @li_dec + POWER(2,@li_power) * CONVERT(integer, @ls_bin_part)
		SET @li_power = @li_power + 1
		SET @li_bin_position = @li_bin_position - 1
	END
	
	SET @lb_binaryhex = @li_dec
	SET @as_base64 = cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))', 'varchar(20)')

	RETURN @as_base64 
END

Open in new window

0
byteboy11
Asked:
byteboy11
  • 4
  • 3
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You would have to change the text in SQL 2000 because the IF does not change that the function of .value is not valid in SQL 2000.

So you would have to eliminate that and replace it with something that does work in SQL 2000.  This does mean that you have 2 different versions of the function, but there is no other way to get SQL to ignore an invalid operator in a downlevel version of SQL.
0
 
byteboy11Author Commented:
Is there a way to use dynamic SQL for that line, like enclose it in quotes or something, based on the SQL version ?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
then you could do it, but you would have to set the variables and use sp_executesql to execute it.  Then if it was in the dynamic sql, sql server would not parse the execution of it, it would just validate that the dynamic sql was closed in quotes.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Just remember when you do what you were trying to do in your original post that when you have a ' inside a ' you have to double them up.

SET @ls_stmt = '  cast(N'''' as xml).value(''xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))'', ''varchar(20)'') '
0
 
byteboy11Author Commented:
Ok I tried changing the function to a procedure since only procedures can use sp_executesql however I'm still getting an error in the code when I try to execute the procedure with:
exec sp_bin2base64 '0100'

the error is:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.



IF EXISTS (SELECT name
     FROM   sysobjects
     WHERE  name = 'sp_bin2base64'
     AND    type = 'P')
    DROP PROCEDURE dbo.sp_bin2base64

go

create procedure [dbo].[sp_bin2base64]
  ( @as_binary_string VARCHAR(40) )
AS
BEGIN
      DECLARE
            @li_power integer,
            @li_bin_position integer,
            @ls_bin_part varchar(4),
            @li_dec integer,
            @ls_hex_string varchar(10),
            @lb_binaryhex varbinary(max),
            @as_base64  varchar(20),
            @ls_stmt      varchar(8000)
                  
      SET @li_bin_position = LEN(@as_binary_string)
      SET @li_power = 0
      SET @li_dec = 0
      
      WHILE @li_bin_position > 0
      BEGIN
            SET @ls_bin_part = SUBSTRING(@as_binary_string, @li_bin_position, 1)
            SET @li_dec = @li_dec + POWER(2,@li_power) * CONVERT(integer, @ls_bin_part)
            SET @li_power = @li_power + 1
            SET @li_bin_position = @li_bin_position - 1
      END
      
      SET @lb_binaryhex = @li_dec

      SET @ls_stmt = ' SET @as_base64 =  cast(N'''' as xml).value(''xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))'', ''varchar(20)'') '    

      -- non dynamic but only works on 2005
      --SET @as_base64 = cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))', 'varchar(20)')

      -- Dynamic - fails with error
      EXECUTE sp_executesql @ls_stmt,N'@as_base64 nvarchar(20) OUTPUT', @as_base64 output
      
      select @as_base64
END

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You just need to change the line

DECLARE @l_stmt nvarchar(4000)
instead of
@l_stmt varchar(8000)
0
 
byteboy11Author Commented:
Changed the declaration but now getting:
Msg 9501, Level 16, State 2, Line 1
XQuery: Unable to resolve sql:variable('@lb_binaryhex'). The variable must be declared as a scalar TSQL variable.

when I call the procedure with: exec sp_bin2base64 '0100'

IF EXISTS (SELECT name
     FROM   sysobjects
     WHERE  name = 'sp_bin2base64'
     AND    type = 'P')
    DROP PROCEDURE dbo.sp_bin2base64

go

create procedure [dbo].[sp_bin2base64]
  ( @as_binary_string VARCHAR(40) )
AS
BEGIN
      DECLARE
            @li_power integer,
            @li_bin_position integer,
            @ls_bin_part varchar(4),
            @li_dec integer,
            @ls_hex_string varchar(10),
            @lb_binaryhex varbinary(1000),
            @as_base64  nvarchar(20),
            @ls_stmt      nvarchar(4000)
                 
      SET @li_bin_position = LEN(@as_binary_string)
      SET @li_power = 0
      SET @li_dec = 0
     
      WHILE @li_bin_position > 0
      BEGIN
            SET @ls_bin_part = SUBSTRING(@as_binary_string, @li_bin_position, 1)
            SET @li_dec = @li_dec + POWER(2,@li_power) * CONVERT(integer, @ls_bin_part)
            SET @li_power = @li_power + 1
            SET @li_bin_position = @li_bin_position - 1
      END
     
      SET @lb_binaryhex = @li_dec

      SET @ls_stmt = ' SET @as_base64 =  cast(N'''' as xml).value(''xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))'', ''varchar(20)'') '    

      -- non dynamic but only works on 2005
      --SET @as_base64 = cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@lb_binaryhex")))', 'varchar(20)')

      -- Dynamic - fails with error
      EXECUTE sp_executesql @ls_stmt,N'@as_base64 nvarchar(20) OUTPUT', @as_base64 output
     
      select @as_base64
END
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now