?
Solved

Improve an existing stored procedure...

Posted on 2013-05-11
10
Medium Priority
?
301 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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