Michael --

SQL access is set up through our internal framework.

This is a very simple test program. lnResult is 1. But, csrAPItems doesn't appear in the current data session.

*     Create SP string with:
*     1.  Table variable.

lcSQL = "declare @csrAPItem  table (fpokey Char (12) NOT NULL) "

*     2.  Insertion of a value into table variable
lcSQL = lcSQL + "INSERT INTO @csrAPItem (fpokey) VALUES ('706652 11  0') " 

*     3.  Join with main table
lcSQL = lcSQL + "SELECT a.* from apitem a inner join @csrAPItem b ON a.fPOKey = b.fPOKey" 

lnResult =  SQLEXEC (lnHandle, lcSQL, "csrAPItems")

>It would be alot easier if you posted an example of what you're trying to do.
>
>-Mike
>
>>Michael --
>>
>>PMFJI, but I must be missing something simple in SP.
>>
>>When I created a SQL temp table and joined on it, I had to put the final query in a separate VFP SQLEXEC -- or a separate batch -- in order to get a result set returned to VFP. Putting all SP commands in a single string returned no records.
>>
>>I'd like to use table variables to reduce overhead and cleanup. But, when using table variables, I find the same behavior -- no result set -- to VFP in a single batch. Separate batches won't work as the variable goes out of scope.
>>
>>For fun, I tried using returning multiple sets, but that didn't appear to work either.
>>
>>Any thoughts?
>>
>>TIA,
>>
>>
>> Jay
>>
>>
>>
>>>If you're using SQL Server 2000, use a table variable instead of the temp table
>>>
>>>DECLARE @temp TABLE (...)
>>>
>>>Make sure that you have an index on _links.link_id.
>>>
>>>Text manipulation is not SQL Server's strength. Build the HTML table somewhere else.
>>>
>>>Can you provide an execution plan?
>>>
>>>-Mike
>>>
>>>>Hi All,
>>>>
>>>>I have a table that contains about 2 million records and has a full text index built on two varchar fields (lengths = 100 and 300, respectively).
>>>>
>>>>Any ideas on how to make this SP run faster?
>>>>(it's now taking about 5 seconds to run)
>>>>
>>>>
>>>>

>>>>CREATE PROCEDURE __get_links
>>>> @intPage int = 1,
>>>> @strSearch varchar(200) = 'free email',
>>>> @strCommonImages varchar(100) = '/images'
>>>>AS
>>>>
>>>>BEGIN
>>>> SET NOCOUNT ON
>>>> SET @strSearch = '"' + @strSearch + '"'
>>>> DECLARE @intStart integer
>>>> SET @intStart = (@intPage * 10) - 10
>>>> CREATE TABLE #temp ( recID int IDENTITY (1,1), link_id int, link_title varchar (100), link_url varchar (400), link_description varchar (300), priority int, rank int )
>>>> INSERT INTO #temp (link_id, link_title, link_url, link_description, priority, rank)
>>>>  SELECT a.link_id, a.link_title, a.link_url, a.link_description, a.link_priority, b.rank
>>>>  FROM live.dbo._links a INNER JOIN
>>>>  CONTAINSTABLE (live.dbo._links, *, @strSearch, 100 ) b
>>>>  ON a.link_id = b.[KEY]
>>>>  ORDER BY b.rank DESC, a.link_priority DESC
>>>>  OPTION (KEEPFIXED PLAN)
>>>>
>>>>
>>>> -- Get our 10 records
>>>>SELECT TOP 10
>>>>      ('<TR><TD width=99% align=left><A class=blueLink8 href=javascript:sendLink("' +CAST(link_id as varchar(10))+ '");><U><B>') as col_1,
>>>>      'col_2' =
>>>>      CASE
>>>>         WHEN LEN(link_title) > 67 THEN LEFT(link_title,67) + '...</B></U></A><BR><FONT class=fntDarkBlue8>'
>>>>         ELSE link_title + '</B></U></A><BR><FONT class=fntDarkBlue8>'
>>>>      END,
>>>>      'col_3' =
>>>>      CASE
>>>>         WHEN LEN(link_description) > 180 THEN LEFT(LEFT(link_description, CHARINDEX('TE_ADULT',link_description)-1),177) + '...'
>>>>         ELSE LEFT(link_description, CHARINDEX('TE_ADULT',link_description)-1)
>>>>      END,
>>>>      ('</FONT><BR></TD><TD width=120><IMG border=0 src=' +@strCommonImages+ '/clear.gif width=120 height=1></TD></TR>') as col_4,
>>>>      ('<TR><TD colspan=3><IMG border=0 src=' +@strCommonImages+ '/clear.gif width=100% height=10></TD></TR>') as col_5
>>>>   FROM #temp WHERE recID > @intStart
>>>> DROP TABLE #temp
>>>>END
>>>>
>>>>

>>>>
>>>>Thanks for any help....
>>>>Mike