Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Add a row in stored procedure...

Posted on 2013-05-13
5
240 Views
Last Modified: 2013-05-13
Stored procedure at http://sqlfiddle.com/#!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.

tbaTarget
========
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28125687.html

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)
values('C','R','Q'),('B','X',NULL),('D',NULL,NULL),('B','P','P'),('A','X',NULL)
go

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

-- Get the column names
SELECT @where = STUFF(( SELECT ',['+ COLUMN_NAME+']'
                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'
exec(@sql)

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(''), 
                type).value('.','nvarchar(max)')
               , 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'
exec(@sql)

--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


exec(@sql)



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


end

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39162698
0
 
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.

Thanks.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39162794
0
 
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:

tblTarget
==========
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.
tblTarget.png
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163017
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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