March 18, 2003 at 7:55 am
Please look at the code below, I know it is incorrect but I am just trying to get the idea across. This can be possibly done with Dynamic SQL, but I was wondering if there is a way with static sql i can achieve it. I have two tables with similar columns and I want to pass in a parameter in SP to figure out what table it should select data from, any help would be greatly appreciated.
Thanks
DECLARE curInstructors CURSOR FAST_FORWARD FOR
SELECT InstructorFirstName,
InstructorLastName,
InstructorEmailAddres
FROM CASE WHEN 1 THEN
tblClass
ELSE
tblCourse
END
March 18, 2003 at 9:31 am
You can't use case for tables names in the from clause, only for columns. To avoid dynamic SQL you could create to SELECT statements (one for each table) and use
IF Cond1
SELECT FROM Table1
ELSE
SELECT FROM Table2
Or also use dynamic t-sql.
March 18, 2003 at 1:21 pm
Try this... hope that helps shed some light...
Billy
/* --- cut here --- */
begin tran
set nocount on
create table aaa(aaa_value int);
insert into aaa(aaa_value) values(1);
insert into aaa(aaa_value) values(2);
insert into aaa(aaa_value) values(3);
create table bbb(bbb_value int);
insert into bbb(bbb_value) values(4);
insert into bbb(bbb_value) values(5);
insert into bbb(bbb_value) values(6);
set nocount off
go
create view view_aaa_bbb as
select aaa_value as the_value, 'aaa' as table_name from aaa
union select bbb_value as the_value, 'bbb' as table_name from bbb
go
print 'select values from table AAA directly'
select * from aaa;
print 'select values from table AAA where value is 1 via view'
print ' should return one record'
select * from view_aaa_bbb where table_name = 'aaa' and the_value = 1
print 'select values from table AAA where value is 4 via view'
print ' should return zero records'
select * from view_aaa_bbb where table_name = 'aaa' and the_value = 4
go
rollback
/* --- cut here --- */
March 19, 2003 at 2:21 am
What conditions whould you use? Is the conditions a column from one table. If YES use user defined function
Hendra
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply