|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-19 : 17:47:19
|
Jason writes "Hi Sean, I've been working for two weeks now trying to get this stored procedure to work. The SQL statement executes just fine on its own, but when I couple it with this stored procedure the thing gives me an error message number 107 saying "The column prefix 'K' does not match with a table name or alias name used in the query." I've read countless articles on why this could be happening and nothing has helped. Please help - I'm running out of resources quickly. We're using SQL 7 on a Win2k machine.
Here's the code:
/*
execute sp_Search
@SQLstatement='SELECT DISTINCT TB.ID, TB.Title, TB.Summary, K.Rank FROM CONTAINSTABLE(tb_Main, *, "ISABOUT',
@SQLstatement2=' (search)',
@SQLstatement3=' ") K INNER JOIN tb_Main TB ON TB.ID = K.[Key]',
@SQLstatement4=' INNER JOIN tb_LangID ON TB.FK_LangID = tb_LangID.LangID INNER JOIN tb_Prod',
@SQLstatement5=' ON TB.ID = tb_Prod.FK_Main INNER JOIN tb_Product',
@SQLstatement6=' ON tb_Prod.FK_Product = tb_Product.ProdID AND tb_LangID.LangID = tb_Product.FK_LangID',
@SQLstatement7=' WHERE (TB.FK_LangID = 1) AND (tb_Prod.FK_Product = 11)',
@start='1',
@inc='10'
*/
ALTER procedure sp_Search
@SQLstatement varchar(255),
@SQLstatement2 varchar(255),
@SQLstatement3 varchar(255),
@SQLstatement4 varchar(255),
@SQLstatement5 varchar(255),
@SQLstatement6 varchar(255),
@SQLstatement7 varchar(255),
@start int,
@inc int = 10
As
Set NoCount on
declare @FinSQL nVarchar(4000)
declare @FinSQL2 nVarchar(4000)
select @FinSQL = "insert into #temp (OriginalID, Title, Summary, Rank) (" + @SQLstatement + @SQLstatement2 + @SQLstatement3 + @SQLstatement4 + @SQLstatement5 + @SQLstatement6 + @SQLstatement7 + ")"
print @FinSQL
create table #temp (ArticleID int not null IDENTITY, OriginalID int, Title varchar(255), Summary varchar(500), Rank int, Total int)
select @FinSQL2 = "insert into #temp2 (OriginalID, Title, Summary, Rank) select OriginalID, Title, Summary, Rank from #temp ORDER BY K.Rank DESC"
create table #temp2 (ArticleID int not null IDENTITY, OriginalID int, Title varchar(255), Summary varchar(500), Total int)
execute sp_executeSQL @FinSQL
declare @TotalResults int
select @TotalResults = (select count(*) from #temp)
update #temp set Total = @TotalResults
execute sp_executeSQL @FinSQL2
update #temp2 set Total = @TotalResults
select * from #temp2 where ArticleID between @start and (@start + @inc -1)" |
|
Insert is not working with CONTAINSTABLE in a stored procedure