asked on

Stored procedure help

Using a very good solution from ScottPletcher at:

https://www.experts-exchange.com/questions/28123556/Stored-procedure-help.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.

Sharath S

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

Mike Eghtebas

ASKER

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

Sharath S

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

Mike Eghtebas

ASKER

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'

Mike Eghtebas

ASKER

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?

Sharath S

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

Mike Eghtebas

ASKER

nothing inserted to tblTarget.

Sharath S

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

Mike Eghtebas

ASKER

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

Mike Eghtebas

ASKER

With your solution at 39157144

tblTarget remains empty.

Sharath S

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?

Mike Eghtebas

ASKER

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

tested here: http://sqlfiddle.com/#!3/35033/3

Mike Eghtebas

ASKER

Beautiful, Thank you very much.

Mike

Mike Eghtebas

ASKER

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

Sharath S

Yes, post the new link for new question.

Mike Eghtebas

ASKER

Please see:

https://www.experts-exchange.com/questions/28124957/Revise-an-existing-stored-procedure-to.html