Solved

Stored Procedure SELECT INTO Parameter

Posted on 2010-08-13
3
1,170 Views
Last Modified: 2012-08-14
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
Comment
Question by:DRRobinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Accepted Solution

by:
chinawal earned 500 total points
ID: 33432530
try

      SELECT TOP 1 @PermitNumber= PermitNo
      FROM tblPermits
      ORDER BY PermitNo;
0
 

Author Closing Comment

by:DRRobinson
ID: 33432611
Thanks!
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33432667
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question