Custom print procedure in MS SQL 2000/7/6.5






2.43/5 (4 votes)
Dec 19, 2003

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