unresolved union!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

unresolved union!!

Author

Topic

kristine
Starting Member

25 Posts

Posted - 2006-08-24 : 03:12:14

is it possible to join a function and a stored procedure?..if it is, how?..and could u give me some brief explanation to picture and try that thing out..thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 03:22:34

You should know how to do it by now, as per this topics of your's, [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70870[/url].

No, it is not possible to do directly.

If you know the output from the stored procedure, create a temporary table with all the columns like this

create table #mytemp (col1 ... , col2 ... ...)

then do a insert to that table with

insert #mytemp exec mystoredprocedure

now you can join that temporary table (with result of stored procedure) with your function, with one of two ways.

1)
select * from #mytemp inner join dbo.fnmyfunction(1) f on f.somecol = #mytemp.someothercol

2)
select *, dbo.fnmyfunction(somecol) from #mytemp

and last, drop table #mytemp

Peter Larsson
Helsingborg, Sweden

kristine
Starting Member

25 Posts

Posted - 2006-08-24 : 22:53:21

am sorry but as i pointed it out with my previous topic..i didn't get what was the advise...anyway, thanx a lot for this though..kinda gave me some direction at least..

just got another question..what if the result set of my stored proc is from a pivoted table..what declarations/parameters(whatever you call the column names thing) should i put in the create #temptable?..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-24 : 23:51:35

The temp table (#mytemp) structure should be same as the result of your stored procedure.
The column name need not be exactactly same as the result from the stored procedure but the data type should.

kristine
Starting Member

25 Posts

Posted - 2006-08-25 : 01:14:32

but i got this error..

Insert Error: Column name or number of supplied values does not match table definition.

if it helps in your consideration..i would want to inform you that the stored proc(absent) i executed creates a temp table and later calls for another stored proc(crosstab) that pivots some of the columns(dates) of the temp table..

prior to the suggestion by PESO, i tested creating another temp table in the query that hopefully contains the result set of my stored proc(absent) joining with a function(totals)..although, so far i haven't tried the joins yet since i couldn't get the result of my temp table containing only the result of the stored proc..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 01:34:39

Leave the original SP alone.
You create a new SP that does the join between the original SP and the function.

Peter Larsson
Helsingborg, Sweden

kristine
Starting Member

25 Posts

Posted - 2006-08-25 : 02:22:30

ok fine..i thought it has something to do with the error i got..so what could have been wrong then if my original stored proc can execute and yet the temp table i created can't?..

i'll show to you the codes..

<code> create table #temptable
(
UserName varchar (100),
GradeLevel varchar(50),
Gender varchar (50),
DOB varchar(15),
Ethniticity varchar (50),
City varchar (50),
ParentsNames varchar (50),
Address varchar (100),
HomePhone varchar (25),
HalfAbsent int,
ClassDate char(8)
)
INSERT INTO #temptable
EXEC HalfAttendance 567, 6577 --@SchoolYearId, @CourseID

drop table #temptable
</code>

haven't added the function yet

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 02:31:39

You can't create a temp table twice with same name within the same connection.

The code you posted look fine. What you do with INSERT statement is that you temporarily store the result of the query in the #Temptable. Nothing happens further. It is just stored there. Nothing shows.

Now you can use that data to join or incorporate a function later.

Peter Larsson
Helsingborg, Sweden

kristine
Starting Member

25 Posts

Posted - 2006-08-25 : 03:28:07

yeah.. i think i have followed that rule..as far as i know, there isn't any existing table or temp table same with that one i've used..

but with the code i posted..i got an error that says..

Insert Error: Column name or number of supplied values does not match table definition.

how come it turns out wrong when in fact the stored proc can be executed well..is there anything else which u could think of that might be affecting the creation of the #temptable?..the columns names and data types that i'ved used are same too..in fact i just copied and pasted it to the temptable so that i wouldn't miss a thing..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 03:46:33

When you run the Stored Procedure, how many columns does the resultset have in total?
How many colums do the temporary table have?

These two numbes must match exactly.

Peter Larsson
Helsingborg, Sweden

kristine
Starting Member

25 Posts

Posted - 2006-08-25 : 04:10:47

ok..let's see..the new sp that i created returns more or less a hundred columns..this is prior to the fact that i made a pivot column out of the dates that i used..well, its group by values includes the names, id's, etc for the students..which is in all 9 columns to the left of the table and the rest are the dates already..hmm, please take a look at the codes i posted previously..the first nine columns are the info for the students, the last two are for the pivoted column and for the population of the data cells under the pivoted column..that's what i expect as return values for the temp table too..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources