Solved

Stored procedure help

Posted on 2013-05-09
18
276 Views
Last Modified: 2013-05-10
Using a very good solution from  ScottPletcher at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28123556.html

in
CREATe TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
)
CREATe TABLE tblTarget(
f1 varchar(10)
,f2 varchar(10)
)

insert into tblSource (f1,f2)
values('c','xx')
,('x','')
,('a','')
,('a','k')



create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
-- if #f1 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f1') IS NOT NULL
    DROP TABLE #f1
-- if #f2 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f2') IS NOT NULL
    DROP TABLE #f2

-- select all distinct f1 values, and store them in a temp table, numbered in ascending value order
SELECT IDENTITY(int, 1, 1) AS f1_id, f1
INTO #f1
FROM (
    SELECT DISTINCT f1
    FROM tblSource 
) AS derived
ORDER BY f1

-- select all distinct f2 values, and store them in a temp table, numbered in ascending value order
SELECT IDENTITY(int, 1, 1) AS f2_id, f2
INTO #f2
FROM (
    SELECT DISTINCT f2
    FROM tblSource 
) AS derived
ORDER BY f2

-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
INSERT INTO dbo.tblTarget ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id

END
delete From tblTarget
exec abcd 'tblSource','tblTarget'
select * from tblSource
select * from tblTarget

Open in new window


I wonder if you could:

Q1: Remove hard coded table names from the stored procedure. Work with the parameters passed to the procedure via: exec abcd 'tblSource','tblTarget' ?

Q2: Exclude null or empty values in tblSource.f1 and tblSource.f2?

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 10
  • 8
18 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39155959
Can you check this?
create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
-- if #f1 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f1') IS NOT NULL
    DROP TABLE #f1
-- if #f2 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f2') IS NOT NULL
    DROP TABLE #f2

-- select all distinct f1 values, and store them in a temp table, numbered in ascending value order
DECLARE @SQL nvarchar(max)
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f1_id, f1
INTO #f1
FROM (
    SELECT DISTINCT f1
    FROM ' + @tblSource +
' ) AS derived
ORDER BY f1'
EXEC(@SQL)
-- select all distinct f2 values, and store them in a temp table, numbered in ascending value order
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f2_id, f2
INTO #f2
FROM (
    SELECT DISTINCT f2
    FROM ' + @tblSource + 
' ) AS derived
ORDER BY f2'
EXEC(@SQL)

-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
INSERT INTO dbo.tblTarget ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id
WHERE COALESCE(f1.f1,'') <> '' AND COALESCE(f1.f2,'')
END

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157002
Thanks for the response. I got an error:

Msg 4145, Level 15, State 1, Procedure abcd, Line 42
An expression of non-boolean type specified in a context where a condition is expected, near 'END'

line:  42  f2.f2_id = f1.f1_id
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157020
try this.
create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
-- if #f1 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f1') IS NOT NULL
    DROP TABLE #f1
-- if #f2 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f2') IS NOT NULL
    DROP TABLE #f2

-- select all distinct f1 values, and store them in a temp table, numbered in ascending value order
DECLARE @SQL nvarchar(max)
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f1_id, f1
INTO #f1
FROM (
    SELECT DISTINCT f1
    FROM ' + @tblSource +
' ) AS derived
ORDER BY f1'
EXEC(@SQL)
-- select all distinct f2 values, and store them in a temp table, numbered in ascending value order
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f2_id, f2
INTO #f2
FROM (
    SELECT DISTINCT f2
    FROM ' + @tblSource + 
' ) AS derived
ORDER BY f2'
EXEC(@SQL)

-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
INSERT INTO dbo.tblTarget ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id
WHERE COALESCE(f1.f1,'') <> '' AND COALESCE(f1.f2,'') <> ''
END

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157041
I was able to alter the stored procedure. But upon running it I get:
-------
(0 row(s) affected)

(3 row(s) affected)

(3 row(s) affected)
Msg 208, Level 16, State 0, Procedure abcd, Line 3
Invalid object name '#f1'.

(4 row(s) affected)
----------
.
.
-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
INSERT INTO dbo.tblTarget ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id
WHERE COALESCE(f1.f1,'') <> '' AND COALESCE(f1.f2,'') <> ''

END

Open in new window

=================
Also I tried:
.
.
,@tbltarget varchar(100)
)
AS
BEGIN
.
.
-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
SELECT @SQL = 'INSERT INTO dbo.[' + @tbltarget + '] ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id
WHERE COALESCE(f1.f1,'') <> '' AND COALESCE(f1.f2,'') <> '''
EXEC(@SQL)
END

Open in new window


But I got:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AND'
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157065
Please not that my last post is updated...

also, shouldn't we use:

SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f1_id, f1
INTO #f1
FROM (
    SELECT DISTINCT f1
    FROM ' + @tblSource +
' ) AS derived
Where f1<>''
ORDER BY f1'

Open in new window


to exclude null entries?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157091
there is a typo in my query. can you try this and see if you get any luck
create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
-- if #f1 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f1') IS NOT NULL
    DROP TABLE #f1
-- if #f2 table exists, drop it, so the SELECT ... INTO below can create it
IF OBJECT_ID('tempdb..#f2') IS NOT NULL
    DROP TABLE #f2

-- select all distinct f1 values, and store them in a temp table, numbered in ascending value order
DECLARE @SQL nvarchar(max)
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f1_id, f1
INTO #f1
FROM (
    SELECT DISTINCT f1
    FROM ' + @tblSource +
' ) AS derived
ORDER BY f1'
EXEC(@SQL)
-- select all distinct f2 values, and store them in a temp table, numbered in ascending value order
SELECT @SQL = 'SELECT IDENTITY(int, 1, 1) AS f2_id, f2
INTO #f2
FROM (
    SELECT DISTINCT f2
    FROM ' + @tblSource + 
' ) AS derived
ORDER BY f2'
EXEC(@SQL)

-- pull ALL f1 and f2 values from the temp table; match them by their sort order: first f1 to first f2, second f1 to second f2, etc.;
-- if either runs out of values, keep listing ALL the other values.
INSERT INTO dbo.tblTarget ( f1, f2 )
SELECT
    f1.f1, f2.f2
FROM #f1 f1
FULL OUTER JOIN #f2 f2 ON
    f2.f2_id = f1.f1_id
WHERE COALESCE(f1.f1,'') <> '' AND COALESCE(f2.f2,'') <> ''
END

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157126
nothing inserted to tblTarget.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157144
I looked into your code on what exactly you are doing. You can get it with this query.
Assuming f1 column exists in @tblsource and f2 column in @tbltarget tables.

alter procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
-- if #f1 table exists, drop it, so the SELECT ... INTO below can create it
declare @sql varchar(max)
select @sql = '
;with cte1 as (select row_number() over (order by f1) rn,* from (select distinct f1 from ' + @tblsource + ') t1),
      cte2 as (select row_number() over (order by f2) rn,* from (select distinct f2 from ' + @tbltarget + ') t1)
INSERT INTO dbo.tblTarget ( f1, f2 )
select c1.f1, c2.f2
  from cte1 c1
  join cte2 c2
    on c1.rn = c2.rn'
exec(@sql)	
end

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157162
am getting:

Msg 4145, Level 15, State 1, Procedure abcd, Line 44
An expression of non-boolean type specified in a context where a condition is expected, near 'END'

BTW, is it possible to run this to test the code?
CREATe TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
)
CREATe TABLE tblTarget(
f1 varchar(10)
,f2 varchar(10)
)

insert into tblSource (f1,f2)
values('c','xx')
,('x','')
,('a','')
,('a','k')

Open in new window


delete From tblTarget
exec abcd 'tblSource','tblTarget'
select * from tblSource
select * from tblTarget

Open in new window


Thanks
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157172
With your solution at  39157144

tblTarget remains empty.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157184
Can you explain what are you trying to achieve? Do you want to load data from source to target? What if the target already has some data?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157195
On the top, there is a link to the original question. Basically:

Put the content of tblSource to tblTarget (distinct and sorted, ignoring blank entries).

In tblSource C matches R where as in tblTarget C doesn't match R and that is okay.

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


 tblSource (exist, the stored procedure expected populated it like base on tblSource)
===============
f1         f2
-----   -------
A          P
B          R
C          X
D          
=========================
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39157260
try this.
alter procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
declare @sql varchar(max)
select @sql = '
;with cte1 as (select row_number() over (order by f1) rn,* from (select distinct f1 from ' + @tblsource + ' where isnull(f1,'''') <> '''') t1),
      cte2 as (select row_number() over (order by f2) rn,* from (select distinct f2 from ' + @tblsource + ' where isnull(f2,'''') <> '''') t1)
INSERT INTO dbo.tblTarget ( f1, f2 )
select c1.f1, c2.f2
  from cte1 c1
  full outer join cte2 c2
    on c1.rn = c2.rn
 order by c1.rn,c2.rn'
exec(@sql)	
end

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157267
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157293
Beautiful, Thank you very much.

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157300
I have a followup question where tblSource has unknown number of fields f1, f2, f3,... fn and tblTarget doesn't exist (the procedure is to build it). Can something like this possible?

I will post the question shortly with a link here.

Thank you
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157322
Yes, post the new link for new question.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157333
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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