Add a row in stored procedure...

Posted on 2013-05-13
Last Modified: 2013-05-13
Stored procedure at!3/e2bd2/1

is called via:
exec abcd 'tblSource','tblTarget',5
select * from tblTarget

Currently, it doesn't included having the second row  <tbd> at each column as shown below. It also adds Null entries for the missing data which is better to be replaced with an empty string.

f1           f2
-------    --------
 <3>       <2>          'note: 2 here means there are two values in this column (P and X)
<tbd>   <tbd>
   A            P
   D            X
   E           Null         ' how to replace Null with empty, so it will not show at all

Question: How can I add this row to tblTarget inside the stored procedure? And how to replace Null values with empty, so that they will not show.

for additional reference, see:

Thank you.

I am including the code here in case the link above has been altered.
create TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
,fn varchar(10)

insert into tblSource (f1,f2,fn)

create procedure abcd
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
,@no_of_cols int
declare @select varchar(max),@where varchar(max),@sql varchar(max)

-- Get the column names
                FROM  INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               ORDER BY   ',['+ COLUMN_NAME+']'
                 FOR XML PATH('')), 1, 1, '') 

-- Create the target table
select @sql = '
IF OBJECT_ID('''+@tblTarget+''') IS NOT NULL
    DROP TABLE ' + @tblTarget +'
	select '+@where+ '--, 0 AS OrderBy 
into '+@tblTarget+' from ' + @tblSource + ' where 1 = 0'

select @select = substring(select_list,1,len(select_list)-5)
  from (
select stuff((select 'SELECT ''' + COLUMN_NAME + ''' colname,'+COLUMN_NAME+' data FROM ' + @tblsource + ' WHERE ISNULL('+COLUMN_NAME+','''') <> '''' UNION '  
                from INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               order by COLUMN_NAME 
                 for xml path(''), 
               , 1, 0, '') as select_list) t1 

select @sql = '
;with cte as (
select *,ROW_NUMBER() over (partition by colname order by data) rn
from ('+@select+') t1)
insert ' + @tblTarget + '
 select distinct '+@where+'--,1
  from cte
pivot (max(data) for colname in ('+@where+') ) p'

--select @sql = '
--INSERT INTO ' + @tblTarget + '
--SELECT COUNT(DISTINCT ' +REPLACE(@where,',','),COUNT(DISTINCT ') + ')FROM ' + @tblTarget

select @sql = 'INSERT INTO ' 
+ @tblTarget 
+ ' SELECT '
+ '''<''' 
+ '+ convert(varchar,COUNT(DISTINCT ' 
+ REPLACE(@where,',',')) + ' + '''>''' + ' ,' + '''<''' + ' + convert(varchar,COUNT(DISTINCT ')
+ ')) + ' 
+ '''>''' 
+ ' FROM '
+ @tblTarget


--select @sql = '
--SELECT ' + @where + '
--FROM ' + @tblTarget + '
--ORDER BY OrderBy'


Open in new window

Question by:Mike Eghtebas
  • 3
  • 2
LVL 41

Expert Comment

ID: 39162698
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39162734
Hi  Sharath_123,

Other than one minor point, it works fine.

The problem is in a column counts where there is 2 items it shows <3>

It increases all column value counts except the very first one.

LVL 41

Accepted Solution

Sharath earned 500 total points
ID: 39162794
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39162981
Thank you. It is excellent.

In a new question I will post shortly, I need to handle sort issue with this.

When I call:  select * from tblTarget Order By 1

I get the result in the attached image for:
create  TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)

create  TABLE tbltarget(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)
--select * from tblSource
insert into tblSource (f1, f2, f3, f4, f5)
values('C','R','Q','Z', 'W'),(NULL,'X',NULL,'GZ', 'HW'),('D',NULL,NULL,'CZ', 'MW'),(NULL,'P','P','IZ', 'UW'),(NULL,'UX',NULL,'GZ', NULL)

Open in new window

If we could add a new sort column called f0 (int type no index) and number it like:

f0    f1   f2    f3  ....
---   ----
0   <4>
0   <tbd>
1      A
2      B

Then we can sort in  by:
select * from tblTarget Order By f0

I will post a link here.

Thank you.
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163017

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question