Combining these 2 short Stored Procedures

CREATE PROCEDURE MyBooks_Selling
(
@.MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@.MemberID

GO
GRANT EXEC
ON MyBooks_Selling
TO bto
GO

CREATE PROCEDURE MyBooks_Buying
(
@.MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@.MemberID

GO
GRANT EXEC
ON MyBooks_Buying
TO bto
GO

Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure

thxSomething like:

CASE @.NewInput
 WHEN 'blah' THEN
 SELECT * FROM Seller
 ELSE
 SELECT * FROM Buyer
END

You'll need to check the exact syntax in Books Online

Cheers
Ken|||thx a lot

Column Names of Fulltext search result

I was trying to do full text search. I have no problem to do the search as
following:
select MemberID, Surname, Firstname from members where FREETEXT(*, N'moore')
It returned all rows for any column containing values that match the
meaning, but not the exact wording, of the text 'moore'.
My problem is that my client want to know the name(s) of the column(s) which
the keyword 'moore' was found. How can I get the required info?
And I also tried FREETEXTTABLE, which only returns a relevance ranking value
(RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
CONTAINSTABLE
Thanks in advance.
To get an exact search, ie moore, but not moores wrap your freetext search
in double quotes or use contains, ie
select MemberID, Surname, Firstname from members where FREETEXT(*,
N'"moore"')
You can't easily get the column where the hit occurs. You would have to do
something like this
create table members(MemberID int identity not null, surname varchar(20),
firstname varchar(20), constraint memberspk primary key (memberid))
GO
insert into members(surname, firstname) values('moore','moore')
insert into members(surname, firstname) values('moore','dave')
insert into members(surname, firstname) values('dave','moore')
insert into members(surname, firstname) values('dave','dave')
GO
sp_fulltext_database 'enable'
GO
create fulltext index on members(surname, firstname) key index memberspk
GO
select *, case
when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
'surname'
when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
'Firstname'
when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
'both'
else 'not sure'
end From members where contains(*,'moore')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>I was trying to do full text search. I have no problem to do the search as
> following:
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'moore')
> It returned all rows for any column containing values that match the
> meaning, but not the exact wording, of the text 'moore'.
> My problem is that my client want to know the name(s) of the column(s)
> which
> the keyword 'moore' was found. How can I get the required info?
> And I also tried FREETEXTTABLE, which only returns a relevance ranking
> value
> (RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
> CONTAINSTABLE
> Thanks in advance.
|||Hilary,
Thank you for your reply. I thought about this way too, but I have problem
with it. I had more than 50 columns to be searched, which means I have to
'when' more than 50 times, actually lots more than 50 times if I need the
column combinations, such as your 'both' case.
I was wondering if there is a straight forward way to do so. If no, I guess
I have to do some programming...
Thanks,
"Hilary Cotter" wrote:

> To get an exact search, ie moore, but not moores wrap your freetext search
> in double quotes or use contains, ie
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'"moore"')
> You can't easily get the column where the hit occurs. You would have to do
> something like this
> create table members(MemberID int identity not null, surname varchar(20),
> firstname varchar(20), constraint memberspk primary key (memberid))
> GO
> insert into members(surname, firstname) values('moore','moore')
> insert into members(surname, firstname) values('moore','dave')
> insert into members(surname, firstname) values('dave','moore')
> insert into members(surname, firstname) values('dave','dave')
> GO
> sp_fulltext_database 'enable'
> GO
> create fulltext index on members(surname, firstname) key index memberspk
> GO
> select *, case
> when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
> 'surname'
> when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
> 'Firstname'
> when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
> 'both'
> else 'not sure'
> end From members where contains(*,'moore')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
> news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>
>
|||You could also limit your FT query to a certain column.
For example
select MemberID, Surname, Firstname from members where
contains(surname,'moore')
and do the same for every fulltext indexed column in your table and then
combine the results.
Thanks
"X. Zhang" wrote:
[vbcol=seagreen]
> Hilary,
> Thank you for your reply. I thought about this way too, but I have problem
> with it. I had more than 50 columns to be searched, which means I have to
> 'when' more than 50 times, actually lots more than 50 times if I need the
> column combinations, such as your 'both' case.
> I was wondering if there is a straight forward way to do so. If no, I guess
> I have to do some programming...
> Thanks,
> "Hilary Cotter" wrote: