Solved

Improve an existing stored procedure...

Posted on 2013-05-11
10
298 Views
Last Modified: 2013-05-12
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.
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
  • 5
  • 5
10 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159299
Hi,

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

Giannis
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39159465
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159539
I have to be honest, i missed that part of the question. I will have a look and let you know...
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39159585
Thank you.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159588
Can you check this?

http://sqlfiddle.com/#!3/04c2f/1
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39159616
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159632
Hi,

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

Giannis
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39159667
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
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39159682
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
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 39159752
You are right.

Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

In this article I will describe the Backup & Restore 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.
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.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

695 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