DRRobinson
asked on
Stored Procedure SELECT INTO Parameter
I want to return a value from a select statement in a stored procedure - I was told in another thread to avoid using cursors whenever possible. I've got the following syntax that does not work.. I just want to have the output parameter receive the PermitNo field returned from the query... What's wrong with this syntax, the error returned is:
Msg 102, Level 15, State 1, Procedure GetNexPermitNo, Line 20
Incorrect syntax near '@PermitNumber'.
Msg 102, Level 15, State 1, Procedure GetNexPermitNo, Line 20
Incorrect syntax near '@PermitNumber'.
USE [SQLBurnPermit]
GO
/****** Object: StoredProcedure [dbo].[GetNexPermitNo] Script Date: 08/13/2010 14:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetNexPermitNo]
@PermitNumber varchar(15) output
-- Add the parameters for the stored procedure here
-- <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
-- <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--DECLARE contact_cursor SCROLL CURSOR FOR
SELECT TOP 1 PermitNo INTO @PermitNumber
FROM tblPermits
ORDER BY PermitNo;
--OPEN contact_cursor asc;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
--FETCH LAST FROM contact_cursor
--INTO @PermitNumber;
RETURN -- @PermitNumber;
-- SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
--CLOSE contact_cursor;
--DEALLOCATE contact_cursor;
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SET @PermitNumber = (SELECT TOP 1 PermitNo
FROM tblPermits
ORDER BY PermitNo);
The syntax you are trying to use can only be used while inserting values into tables
FROM tblPermits
ORDER BY PermitNo);
The syntax you are trying to use can only be used while inserting values into tables
ASKER