Link to home
Start Free TrialLog in
Avatar of DRRobinson
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'.
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chinawal
chinawal
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DRRobinson
DRRobinson

ASKER

Thanks!
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