Custom print procedure in MS SQL 2000/7/6.5

Introduction

SQL server print function gives you option to print any data type value/variable etc. But what if you want to print multiple variables or values in a single print statement and of different datatypes. For example int and varchar. You have to use convert for every variable and make target as varchar.

Background

This problem comes to me when I am working on migration from Sybase to SQL server? Sybase has excellent print function which can print various type of local variable in a single print statement. So I have written a SQL procedure which can print maximum 20 local variable in a single call? Use it and enjoy.

Using the code

Just run this script in Query analyzer.

/*---------------------------------------------------------------*/
-- version inf 1.0

-- creation date 18/12/2003

-- created by Abhay dubey

-- purpose this procedure is capable to print 

-- any type of argument which can be from 0 to 20 as a string.  

-- drop procedure adPrint

/*------------------------------------------------------------*/
CREATE procedure adPrint  (
    @par1i sql_variant ='No value supplied in advance print' ,
    @par2i sql_variant =  '0',@par3i sql_variant =  '0',
    @par4i sql_variant =  '0',
    @par5i sql_variant =  '0',@par6i sql_variant =  '0',
    @par7i sql_variant =  '0',@par8i sql_variant =  '0',
    @par9i sql_variant =  '0',@par10i sql_variant =  '0',
    @par11i sql_variant =  '0',@par12i sql_variant =  '0',
    @par13i sql_variant =  '0',@par14i sql_variant =  '0',
    @par15i sql_variant =  '0',@par16i sql_variant =  '0',
    @par17i sql_variant =  '0',@par18i sql_variant =  '0',
    @par19i sql_variant =  '0',@par20i sql_variant =  '0'
  )
AS
BEGIN
   DECLARE @var1 nvarchar(4000)
   DECLARE @par1 nvarchar(255),
    @par2 nvarchar(255),
    @par3 nvarchar(255),
    @par4 nvarchar(255),
    @par5 nvarchar(255),
    @par6 nvarchar(255),
    @par7 nvarchar(255),
    @par8 nvarchar(255),
    @par9 nvarchar(255),
    @par10 nvarchar(255),
    @par11 nvarchar(255),
    @par12 nvarchar(255),
    @par13 nvarchar(255),
    @par14 nvarchar(255),
    @par15 nvarchar(255),
    @par16 nvarchar(255),
    @par17 nvarchar(255),
    @par18 nvarchar(255),
    @par19 nvarchar(255),
    @par20 nvarchar(255)
   
 --This portion of the code should be added in procedure 

 --to handle null parameters.

 select @par1 = convert(varchar(255),isnull(@par1i,'0'))
 select @par2 = convert(varchar(255),isnull(@par2i,'0'))
 select @par3 = convert(varchar(255),isnull(@par3i,'0'))
 select @par4 = convert(varchar(255),isnull(@par4i,'0'))
 select @par5 = convert(varchar(255),isnull(@par5i,'0'))
 select @par6 = convert(varchar(255),isnull(@par6i,'0'))
 select @par7 = convert(varchar(255),isnull(@par7i,'0'))
 select @par8 = convert(varchar(255),isnull(@par8i,'0'))
 select @par9 = convert(varchar(255),isnull(@par9i,'0'))
 select @par10 = convert(varchar(255),isnull(@par10i,'0'))
 select @par11 = convert(varchar(255),isnull(@par11i,'0'))
 select @par12 = convert(varchar(255),isnull(@par12i,'0'))
 select @par13 = convert(varchar(255),isnull(@par13i,'0'))
 select @par14 = convert(varchar(255),isnull(@par14i,'0'))
 select @par15 = convert(varchar(255),isnull(@par15i,'0'))
 select @par16 = convert(varchar(255),isnull(@par16i,'0'))
 select @par17 = convert(varchar(255),isnull(@par17i,'0'))
 select @par18 = convert(varchar(255),isnull(@par18i,'0'))
 select @par19 = convert(varchar(255),isnull(@par19i,'0'))
 select @par20 = convert(varchar(255),isnull(@par20i,'0'))
 ----This portion of the code should be added in procedure 

 -- to handle null parameters.

 select  @var1 = @par1 
 if(ltrim(rtrim(@par2)) = '0')
 begin
  select  @var1 = @par1 
  goto print_here
  --RETURN(@var1)

 end
 else if(ltrim(rtrim(@par3)) = '0')
 begin
  select @var1 = @par1 + @par2 
  goto print_here
  
 end
 else if(ltrim(rtrim(@par4)) = '0')
 begin
  select @var1 = @par1 + @par2 + @par3
  goto print_here
  
 end
 else if(ltrim(rtrim(@par5)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4
  goto print_here
  
 end
 
--------------------------------------------------------------

 else if(ltrim(rtrim(@par6)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5
  goto print_here
  
 end
 else if(ltrim(rtrim(@par7)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6
  goto print_here
  
 end
 else if(ltrim(rtrim(@par8)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6 + @par7
  goto print_here
  
 end
 else if(ltrim(rtrim(@par9)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6 + 
   @par7 + @par8
  goto print_here
  
 end
 else if(ltrim(rtrim(@par10)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6 +
      @par7 + @par8 + @par9
  goto print_here
  
 end
 else if(ltrim(rtrim(@par11)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
         @par6 + @par7 + @par8 + @par9 + @par10
  goto print_here
  
 end
 else if(ltrim(rtrim(@par12)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 +
      @par6 + @par7 + @par8 + @par9 + @par10 + @par11
  goto print_here
  
 end
 else if(ltrim(rtrim(@par13)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
        @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + @par12
  goto print_here
  
 end
 else if(ltrim(rtrim(@par14)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 +
      @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + @par12 + @par13
  goto print_here
  
 end
 else if(ltrim(rtrim(@par15)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6 + 
    @par7 + @par8 + @par9 + @par10 + @par11 + @par12 + @par13 + @par14
  goto print_here
  
 end
 else if(ltrim(rtrim(@par16)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + @par6 + @par7 + 
    @par8 + @par9 + @par10 + @par11 + @par12 + @par13 + @par14 + @par15
  goto print_here
  
 end
 else if(ltrim(rtrim(@par17)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
       @par6 + @par7 + @par8 + @par9 + @par10 + @par11 +
       @par12 + @par13 + @par14 + @par15 + @par16
  goto print_here
  
 end
 else if(ltrim(rtrim(@par18)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
       @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + 
       @par12 + @par13 + @par14 + @par15 + @par16 + @par17 
  goto print_here
  
 end
 else if(ltrim(rtrim(@par19)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
       @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + 
        @par12 + @par13 + @par14 + @par15 + @par16 + @par17 + @par18
  goto print_here
  
 end
 else if(ltrim(rtrim(@par20)) = '0')
  begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
       @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + 
       @par12 + @par13 + @par14 + @par15 + @par16 + @par17 + @par18 + @par19
  goto print_here
  
 end
----------------------------------------------------------------

 else
 begin
  select @var1 = @par1 + @par2 + @par3 + @par4 + @par5 + 
       @par6 + @par7 + @par8 + @par9 + @par10 + @par11 + 
       @par12 + @par13 + @par14 + @par15 + @par16 + @par17 +
       @par18 + @par19 + @par20
  goto print_here
  
 end
 
 print_here:
 print  @var1
 --print len(@var1)

        
END
  

GO

Points of Interest

I felt that sql_variant makes a programmer free to write code for any data type conversion.