Click here to Skip to main content
15,881,852 members
Search within:


Articles / Database Development / SQL Server
 
Article

Custom print procedure in MS SQL 2000/7/6.5

Rate me:
Please Sign up or sign in to vote.
2.43/5 (4 votes)
23 Dec 2003 47K   16   5
This procedure can be called in another Stored procedure or function and quite handy in debugging and development

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.

SQL
/*---------------------------------------------------------------*/
-- 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Abhay Dubey
Web Developer
India India
I started programming in VC++ and MS Sql server in Early 2000.I have done B.Tech in Electrical Engineering and M.Tech in Computer engineering.I love to work with RDBMS.I have plan to go for P.H.D.

Comments and Discussions

 
First Prev Next
QuestionSlightly modified version, so non NULL values after a NULL value are not ignored Pin
Member 1165283219-Mar-18 10:36
professionalMember 1165283219-Mar-18 10:36 
AnswerRe: Slightly modified version, so non NULL values after a NULL value are not ignored Pin
Member 1165283219-Mar-18 10:38
professionalMember 1165283219-Mar-18 10:38 
GeneralIn case of Null parameter value supplied Pin
Abhay Dubey23-Dec-03 17:28
Abhay Dubey23-Dec-03 17:28 
GeneralSQL Server version Pin
Richard Turnbull22-Dec-03 13:41
Richard Turnbull22-Dec-03 13:41 
GeneralRe: SQL Server version Pin
Krzemo23-Dec-03 16:45
Krzemo23-Dec-03 16:45 
Even worse, if one of the parameters value was NULL all parameters would be ignored
Last Visit: 31-Dec-99 18:00     Last Update: 19-Apr-24 13:03Refresh1

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 18 Dec 2003

Article Copyright 2003 by Abhay Dubey
Everything else Copyright © CodeProject, 1999-2024

Web04 2.8:2024-04-02:1