Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Procedure SELECT INTO Parameter

Posted on 2010-08-13
3
Medium Priority
?
1,173 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

610 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