Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1176
  • Last Modified:

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

0
DRRobinson
Asked:
DRRobinson
1 Solution
 
chinawalCommented:
try

      SELECT TOP 1 @PermitNumber= PermitNo
      FROM tblPermits
      ORDER BY PermitNo;
0
 
DRRobinsonAuthor Commented:
Thanks!
0
 
vdr1620Commented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now