Improve an existing stored procedure...

Once again, using a good solution from Sharath_123 at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28124957.html
Store procedure abcd is called like: (tested samples: http://sqlfiddle.com/#!3/cd529/2)

exec abcd 'tblSource','tblTarget', 2     '<--  instead of 2 you can have 1, 3, ... here
select * from tblSource
select * from tblTarget

Where:

tblSource (exists in the database)
===============
f1         f2              fn
-----   -------   ... ----------
C          R               Q
B          X              
D          
B          P              P
A          X


 tblTarget (doesn't exist, the stored procedure expected build and populated it)
===============
f1         f2              '<-- because we have specified to handle 2 columns only
-----   -------  
A          P            
B          R            
C          X
D          
=========================
Note, in tblSource C matches R where as in tblTarget C doesn't match R and that is okay.

Question 1: With exec abcd 'tblSource','tblTarget', 1 (one column only), it doen't produce distinct records. How can I correct this. See below:

 tblTarget
===============
f1        
-----    
A                      
B    *      
B    * not distinct        
C          
D      

Question 2: How can I add value count at first row as it shown below:

 tblTarget
===============
f1         f2              
-----   -------  
(4)        (3)
A          P            
B          R            
C          X
D

Hear is the code to be revised:
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+ ' 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 '+@where+'
  from cte
pivot (max(data) for colname in ('+@where+') ) p'
exec(@sql)
end

Open in new window

Thank you.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
You do get the counts but they are at the bottom. Plus i have added a new column 'OrderBy',, in order to define the order by.

See this:
http://sqlfiddle.com/#!3/74c24/2
0
 
Ioannis ParaskevopoulosCommented:
Hi,

Can you check this variation of the above fiddle?
http://sqlfiddle.com/#!3/20cee/4

Giannis
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Giannis,

Yes, exec abcd 'tblSource','tblTarget', 1 works nicely now.

Do you have time to take a look at:

tblTarget
===============
f1         f2              
-----   -------  
(4)        (3)       <--- number of values?
A          P  
B          R            
C          X
D
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Ioannis ParaskevopoulosCommented:
I have to be honest, i missed that part of the question. I will have a look and let you know...
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
 
Ioannis ParaskevopoulosCommented:
Can you check this?

http://sqlfiddle.com/#!3/04c2f/1
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Yes I saw. Thank you for the code.

Is it possible to add, say, as union query as top row like
tblTarget
===============
f1         f2              
-----   -------  
(4)        (3)       <--- number of values?
A          P  
B          R            
C          X
D
0
 
Ioannis ParaskevopoulosCommented:
Hi,

I misunderstood the previous question, but check this now: http://sqlfiddle.com/#!3/d1d55/1

Giannis
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
abcd is not supposed to display tblTarget. It is just supposed to populate it.
exec abcd 'tblSource','tblTarget',5

When I run
select * from tblTarget
outside abcd , I don't get the counts.

?
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
You are right.

Thank you.
0
All Courses

From novice to tech pro — start learning today.