Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored procedure help

Posted on 2013-05-09
18
Medium Priority
?
289 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
[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
  • 10
  • 8
18 Comments
 
LVL 41

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 34

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 41

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 34

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 34

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 41

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 34

Author Comment

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

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 34

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

Author Comment

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

tblTarget remains empty.
0
 
LVL 41

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 34

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 41

Accepted Solution

by:
Sharath earned 2000 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 41

Expert Comment

by:Sharath
ID: 39157267
0
 
LVL 34

Author Comment

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

Mike
0
 
LVL 34

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 41

Expert Comment

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

Author Comment

by:Mike Eghtebas
ID: 39157333
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

721 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