March 6, 2008 at 2:07 pm
Accidently posted this in SQL 2000, but am using 2005.
I have a stored procedure that runs a dynamic sql query. The procedure works great and produces the results I expect.
I would like to join the results of this procedure to a table, but have been unable to do it. I think I may be able to do it with a temporary table, but some of the fields will be dynamically populated everytime the procedure is run. I won't know the field names ahead of time.
Any advice would be helpful.
March 7, 2008 at 4:58 pm
I would like to join the results of this procedure to a table, but have been unable to do it. I think I may be able to do it with a temporary table, but some of the fields will be dynamically populated everytime the procedure is run. I won't know the field names ahead of time.
Is there only one table you want to join the sp's results to or are there several and the one to use depends on some condition?
Assuming you have only one table, is it safe to say that no matter how many fields the sp makes, both the "other" table and the sp results will always share a field? Do you know that field in advance?
To do a join you will have to put the sp's results in a table and you will have to build that table in your sp. Depending on your data you should be able to build a table on the fly. This is a lot simpler if all of the fields are dates or all of them are varchar and so forth or if you can tell based on a naming convention. It can still be done even if none of that is available.
Make friends with information_schema.columns.
Pseudo code for spMain:
make #temp1 and give it the known, common column or at least 1 dummy column.
call your sp with the dynamic sql. In addition to whatever it does now, make it add columns to #temp1 and fill #temp1 with the results you want.
join the information_schema.columns where table_name is #temp1 with information_schema.columns for your fixed table on the column names and build a dynamic sql query out of that.
March 8, 2008 at 9:29 am
By having varying column names you have a difficult task. I think ksullivan's reply will work, but it does not sound very scalable. Does your "dynamic" sp have fixed number of columns returned or does that vary as well? If there is a fixed number of columns you could, although not the best performance wise, create generic temp table with your join field of the correct type, assuming it remains constant, and then varchar types for the rest of the columns.
Is there a way to do this without the dynamic sql?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2008 at 12:32 am
Since the sp runs a dynamic query and field names are not known in advance, it's impossible to do a join, unless the sp always returns at least the link field with a predefined name.
In mssql you have to use table valued function, if you want to join it to something else:
create function X(@P1 integer) returns @RetTable TABLE(Field1 integer, field2 varchar(100),...)
as begin
insert into @RetTable ....
return
end;
select * from table1 T1 join X(123) X1 on T1.field1=X1.field1
This is fine if the function result is a small table, otherwise it's a lot better to code the join into stored procedure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply