Solved

Stored Procedure SELECT INTO Parameter

Posted on 2010-08-13
3
1,167 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
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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