|
-- version inf 1.0r
-- 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
--
-- Modified by Mario Osorio on March 19 2018
-- The changes allow for NULL values to exist among the parameters.
-- The opriginal version would print all parameters before the
-- first NULL one.
-- With this change, the procedure will represent null values with
-- the string '[NULL]' and print ALL parameters, even if there is
-- a NULL value in between them.
-- For example:
-- If you call the stored proc this way:
--
-- execute AdPrint 'First Value', NULL, 'Second Value'
--
-- The output would be:
--
-- FirstValue
-- With my proposed changes, it would print:
--
-- FirstValueSecondValue
--
ALTER procedure adPrint (
@par1i sql_variant = NULL, @par2i sql_variant = NULL,
@par3i sql_variant = NULL, @par4i sql_variant = NULL,
@par5i sql_variant = NULL, @par6i sql_variant = NULL,
@par7i sql_variant = NULL, @par8i sql_variant = NULL,
@par9i sql_variant = NULL, @par10i sql_variant = NULL,
@par11i sql_variant = NULL, @par12i sql_variant = NULL,
@par13i sql_variant = NULL, @par14i sql_variant = NULL,
@par15i sql_variant = NULL, @par16i sql_variant = NULL,
@par17i sql_variant = NULL, @par18i sql_variant = NULL,
@par19i sql_variant = NULL, @par20i sql_variant = NULL
)
AS
BEGIN
DECLARE @var1 nvarchar(4000)
DECLARE @par0 nvarchar(max)
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,NULL))
select @par2 = convert(varchar(255),isnull(@par2i,NULL))
select @par3 = convert(varchar(255),isnull(@par3i,NULL))
select @par4 = convert(varchar(255),isnull(@par4i,NULL))
select @par5 = convert(varchar(255),isnull(@par5i,NULL))
select @par6 = convert(varchar(255),isnull(@par6i,NULL))
select @par7 = convert(varchar(255),isnull(@par7i,NULL))
select @par8 = convert(varchar(255),isnull(@par8i,NULL))
select @par9 = convert(varchar(255),isnull(@par9i,NULL))
select @par10 = convert(varchar(255),isnull(@par10i,NULL))
select @par11 = convert(varchar(255),isnull(@par11i,NULL))
select @par12 = convert(varchar(255),isnull(@par12i,NULL))
select @par13 = convert(varchar(255),isnull(@par13i,NULL))
select @par14 = convert(varchar(255),isnull(@par14i,NULL))
select @par15 = convert(varchar(255),isnull(@par15i,NULL))
select @par16 = convert(varchar(255),isnull(@par16i,NULL))
select @par17 = convert(varchar(255),isnull(@par17i,NULL))
select @par18 = convert(varchar(255),isnull(@par18i,NULL))
select @par19 = convert(varchar(255),isnull(@par19i,NULL))
select @par20 = convert(varchar(255),isnull(@par20i,NULL))
----This portion of the code should be added in procedure
-- to handle null parameters.
select @var1 = @par1
set @par0=
ISNULL(@par2, '')+
ISNULL(@par3, '')+
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 ='')
begin
select @var1 = isnull(@par1, 'Nothing to print...')
goto print_here
end
set @par0=
ISNULL(@par3, '')+
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')+char(13)+
ISNULL(@par15, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')+char(13)+
ISNULL(@par15, '[NULL]')+': '+
ISNULL(@par16, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')+char(13)+
ISNULL(@par15, '[NULL]')+': '+
ISNULL(@par16, '[NULL]')+char(13)+
ISNULL(@par17, '[NULL]')+': [NULL] '
goto print_here
end
set @par0=
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')+char(13)+
ISNULL(@par15, '[NULL]')+': '+
ISNULL(@par16, '[NULL]')+char(13)+
ISNULL(@par17, '[NULL]')+': '+
ISNULL(@par18, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+': '+
isnull(@par2, '[NULL]')+char(13)+
isnull(@par3, '[NULL]')+': '+
isnull(@par4, '[NULL]')+char(13)+
isnull(@par5, '[NULL]')+': '+
isnull(@par6, '[NULL]')+char(13)+
isnull(@par7, '[NULL]')+': '+
isnull(@par8, '[NULL]')+char(13)+
isnull(@par9, '[NULL]')+': '+
isnull(@par10, '[NULL]')+char(13)+
isnull(@par11, '[NULL]')+': '+
isnull(@par12, '[NULL]')+char(13)+
isnull(@par13, '[NULL]')+': '+
isnull(@par14, '[NULL]')+char(13)+
ISNULL(@par15, '[NULL]')+': '+
ISNULL(@par16, '[NULL]')+char(13)+
ISNULL(@par17, '[NULL]')+': '+
ISNULL(@par18, '[NULL]')+char(13)+
ISNULL(@par19, '[NULL]')+': [NULL] '
goto print_here
end
print_here:
print @var1
print ''
--print len(@var1)
END
GO
|
|
|
|
|
Actually Following is the version I'm proposing. What I posted before is another version adapted to my specific needs...
-- 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
--
-- Modified by Mario Osorio on March 19 2018
-- The changes allow for NULL values to exist among the parameters.
-- The opriginal version would print all parameters before the
-- first NULL one.
-- With this change, the procedure will represent null values with
-- the string '[NULL]' and print ALL parameters, even if there is
-- a NULL value in between them.
-- For example:
-- If you call the stored proc this way:
--
-- execute AdPrint 'First Value', NULL, 'Second Value'
--
-- The output would be:
--
-- FirstValue
-- With my proposed changes, it would print:
--
-- FirstValueSecondValue
--
alter procedure adPrint (
@par1i sql_variant = NULL, @par2i sql_variant = NULL,
@par3i sql_variant = NULL, @par4i sql_variant = NULL,
@par5i sql_variant = NULL, @par6i sql_variant = NULL,
@par7i sql_variant = NULL, @par8i sql_variant = NULL,
@par9i sql_variant = NULL, @par10i sql_variant = NULL,
@par11i sql_variant = NULL, @par12i sql_variant = NULL,
@par13i sql_variant = NULL, @par14i sql_variant = NULL,
@par15i sql_variant = NULL, @par16i sql_variant = NULL,
@par17i sql_variant = NULL, @par18i sql_variant = NULL,
@par19i sql_variant = NULL, @par20i sql_variant = NULL
)
AS
BEGIN
DECLARE @var1 nvarchar(4000)
DECLARE @par0 nvarchar(max)
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,NULL))
select @par2 = convert(varchar(255),isnull(@par2i,NULL))
select @par3 = convert(varchar(255),isnull(@par3i,NULL))
select @par4 = convert(varchar(255),isnull(@par4i,NULL))
select @par5 = convert(varchar(255),isnull(@par5i,NULL))
select @par6 = convert(varchar(255),isnull(@par6i,NULL))
select @par7 = convert(varchar(255),isnull(@par7i,NULL))
select @par8 = convert(varchar(255),isnull(@par8i,NULL))
select @par9 = convert(varchar(255),isnull(@par9i,NULL))
select @par10 = convert(varchar(255),isnull(@par10i,NULL))
select @par11 = convert(varchar(255),isnull(@par11i,NULL))
select @par12 = convert(varchar(255),isnull(@par12i,NULL))
select @par13 = convert(varchar(255),isnull(@par13i,NULL))
select @par14 = convert(varchar(255),isnull(@par14i,NULL))
select @par15 = convert(varchar(255),isnull(@par15i,NULL))
select @par16 = convert(varchar(255),isnull(@par16i,NULL))
select @par17 = convert(varchar(255),isnull(@par17i,NULL))
select @par18 = convert(varchar(255),isnull(@par18i,NULL))
select @par19 = convert(varchar(255),isnull(@par19i,NULL))
select @par20 = convert(varchar(255),isnull(@par20i,NULL))
----This portion of the code should be added in procedure
-- to handle null parameters.
select @var1 = @par1
set @par0=
ISNULL(@par2, '')+
ISNULL(@par3, '')+
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 ='')
begin
select @var1 = isnull(@par1, 'Nothing to print...')
goto print_here
end
set @par0=
ISNULL(@par3, '')+
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par4, '')+
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par5, '')+
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par6, '')+
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par7, '')+
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par8, '')+
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par9, '')+
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par10, '')+
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par11, '')+
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par12, '')+
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par13, '')+
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par14, '')+
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par15, '')+
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par16, '')+
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')+
ISNULL(@par15, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par17, '')+
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')+
ISNULL(@par15, '[NULL]')+
ISNULL(@par16, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par18, '')+
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')+
ISNULL(@par15, '[NULL]')+
ISNULL(@par16, '[NULL]')+
ISNULL(@par17, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par19, '')+
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')+
ISNULL(@par15, '[NULL]')+
ISNULL(@par16, '[NULL]')+
ISNULL(@par17, '[NULL]')+
ISNULL(@par18, '[NULL]')
goto print_here
end
set @par0=
ISNULL(@par20, '')
if(@par0 = '')
begin
select @var1 =
isnull(@par1, '[NULL]')+
isnull(@par2, '[NULL]')+
isnull(@par3, '[NULL]')+
isnull(@par4, '[NULL]')+
isnull(@par5, '[NULL]')+
isnull(@par6, '[NULL]')+
isnull(@par7, '[NULL]')+
isnull(@par8, '[NULL]')+
isnull(@par9, '[NULL]')+
isnull(@par10, '[NULL]')+
isnull(@par11, '[NULL]')+
isnull(@par12, '[NULL]')+
isnull(@par13, '[NULL]')+
isnull(@par14, '[NULL]')+
ISNULL(@par15, '[NULL]')+
ISNULL(@par16, '[NULL]')+
ISNULL(@par17, '[NULL]')+
ISNULL(@par18, '[NULL]')+
ISNULL(@par19, '[NULL]')
goto print_here
end
print_here:
print @var1
print ''
--print len(@var1)
END
GO
|
|
|
|
|
we should use following code to handle NULL parameters so jsut modify the code.
/*Add this code */
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 only works for SQL Server 2000. The data type sql_variant was introduced in that version.
I like the idea behind this procedure, but what would happen if one of the parameters value was '0'? I suspect that all following parameters would be ignored.
Richard Turnbull
|
|
|
|
|
Even worse, if one of the parameters value was NULL all parameters would be ignored
|
|
|
|
|