[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Improve an existing stored procedure...

Posted on 2013-05-11
10
Medium Priority
?
305 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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