syntax of sp_prepexec, sp_execute
Mary Gayle Greene
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","OTHAGENID",
"FromFPW","prevdown","tempifpssysid","upsize_ts"
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
Erland Sommarskog
Mary Gayle Greene (gree...@charter.net) writes:
> 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","OTHAGENID",
> "FromFPW","prevdown","tempifpssysid","upsize_ts"
> 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...
sp_prepexec, sp_execute and a few more, exists at the convenience for
various client libraries, and any use of them on their own, is likely
to completely unsupported. So while it may fun to play with, I would
discourage you from using it production code.
However, I'm inclined to believe that the syntax is similar to the
one of sp_executesql, in that there is a parameter which declares
the parameter list, a statement, and the remaining parameters comes
from the parameter list you declared.
> why is @P1 used for both the parameter within the select query and the
> name of the prepared statement?
I would guess @P1 is just a generic name, and they have to relation
to each other. I may be wrong, but my belief is that the outer @P1
is invented by the Profiler to show the output value of the parameter.
> 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
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'
--
Erland Sommarskog, SQL Server MVP, som...@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
fold...@yahoo.co.uk
> > set @P1=1
> > exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT
> > "totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENID",
> > "FromFPW","prevdown","tempifpssysid","upsize_ts"
> > 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.