syntax of sp_prepexec, sp_execute

[Mary Gayle Greene]

The following was captured from SQL Profiler during an interaction between
Access Xp and SQL Server 2000:

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT
"totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENI D",
"FromFPW","prevdown","tempifpssysid","upsize_t s"
FROM "dbo"."Family"
WHERE "totalid" = @P1', N'-:1044463096'
select @P1
go

I think I understand that sp_prepexec prepares a parameterized query with
@P1 as a variable...but sp_prepexec is not in Books Online...

could someone please explain the syntax of sp_prepexec?

why is @P1 used for both the parameter within the select query and the name
of the prepared statement?

the other seemingly strange thing, is that when I run the above in Query
Analyzer and try to use the parameterized query like so

exec sp_execute 1, N'-:1044463096'
go

I receive the error message

Could not find prepared statement with handle 1

[foldface]

> > declare @P1 int

set @P1=1
exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT
"totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENI D",
"FromFPW","prevdown","tempifpssysid","upsize_t s"
FROM "dbo"."Family"
WHERE "totalid" = @P1', N'-:1044463096'
select @P1
go I receive the error message

Could not find prepared statement with handle 1

Did you run sp_prepexec in the same session? I would guess that the handle
is session-specific. I was able to get this to work:

declare @x int
exec sp_prepexec @x OUTPUT, N'@pid nchar(5)',
N'SELECT * FROM Customers WHERE CustomerID = @pid', @pid = N'ALFKI'
select @x
exec sp_execute @x, N'BERGS'

I'm looking at something similiar, but I'm a newbie to SQL. Anyway I wanted
to capture messages to the database from an application and replay them
later. I got the same message.
It seem you can get it to work as long as you remove the lines with:

set @P1=1

in them. I did this with a chunky bit of sql and it seemed happy.