I don't think you need to use EXECUTE for this. Just use the variable in
the SELECT statement like:
SELECT @var = SUM(Hours) FROM Staff WHERE UserID = @par
or
SELECT @var = SUM(Hours) FROM Staff WHERE UserID = CAST(@par AS int)
depending on what Type UserID is.
With a more complex WHERE clause I would do the EXECUTE() into a temporary
table and then retrieve the value into @hours from the temporary table.
Paul Ritchie
Radio Computing Services.
> -----Original Message-----
> From: Stephen Bertram [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, 14 March 2001 5:27 p.m.
> To: Multiple recipients of list database
> Subject: [DUG-DB]: Stored procedure question
>
>
> Hi
>
> I'm using MSSQL7 and want to write a stored procedure that generates a
> SELECT statement returning a single value to a variable.
>
> Example :
>
> CREATE PROCEDURE Sample
> @par varchar(255),
> @hours int OUTPUT
> AS
> DECLARE @var int,
> @where varchar(255)
> SET @where = ' WHERE UserID = ' + @par
> EXECUTE('SELECT @var = SUM(Hours) FROM Staff' + @where)
> SET @hours = @var
>
> This fails as @var is not recognised.
>
> How can I return a value from the EXECUTE statement?
>
> TIA
>
> Stephen
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
