Solved

Improve an existing stored procedure...

Posted on 2013-05-11
10
287 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
  • 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 33

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

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 33

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 33

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 33

Author Closing Comment

by:Mike Eghtebas
ID: 39159752
You are right.

Thank you.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now