Solved

Add a row in stored procedure...

Posted on 2013-05-13
5
247 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
[X]
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
  • 3
  • 2
5 Comments
 
LVL 41

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 41

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

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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