Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored procedure help

Posted on 2013-05-09
18
Medium Priority
?
291 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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 …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
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. …

916 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