Solved

Change Stored Procedure Output Type

Posted on 2010-08-13
6
558 Views
Last Modified: 2012-05-10
I very new to stored procedures..  I've created the follow procedure, everything seems to work OK, but I get an error on the RETURN:how do I change the output type from int to a string varchar?

Msg 245, Level 16, State 1, Procedure GetNexPermitNo, Line 31
Conversion failed when converting the varchar value '10-3198' to data type int.

USE [SQLBurnPermit]
GO
/****** Object:  StoredProcedure [dbo].[GetNexPermitNo]    Script Date: 08/13/2010 11:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetNexPermitNo]
	-- 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;
	DECLARE @PermitNumber varchar(15);

    -- Insert statements for procedure here
	DECLARE contact_cursor SCROLL CURSOR FOR

	SELECT PermitNo FROM tblPermits 
	ORDER BY PermitNo;
	OPEN contact_cursor;

-- 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
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33430917
Please see the attached query with your sample data and final query

Raj
create table #table
(
	TicketID              varchar(20),
	SetStatus             varchar(20), 
	SetDate               datetime,                        
	Count				int
)

insert into #table
select '100601BG15',      'Acknowledged',                      '2010-06-01 14:02:45.330',                   1
union all
select '100601BG15',      'Waiting on Business',      '2010-06-01 14:07:28.820',                   0 
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:07:40.597',                   1
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:34:43.737',                   1
union all
select '100601BG15',      'Pending',                           '2010-06-03 18:29:47.120',                   1      
union all
select '100601BG15',      'Post Dated',                      '2010-06-04 09:00:52.587',                   0
union all
select '100601BG15',      'Post Dated',                      '2010-06-07 12:42:37.960',                   0 
union all
select '100601BG15',      'Fulfilled',                              '2010-06-07 14:38:29.677',                   1




with cte as
(
	select *, row_number() over (order by SetDate) row_no 
	from #table
) 
select *, datediff(mi, a.SetDate, b.SetDate) as DiffMinute from cte a
	inner join cte b
		on a.row_no = b.row_no - 1

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33430951
Oops! Ignore my post.  Posted to other question than I intended

Raj

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 33431118
you can't
the return code from a stored procedure is an integer and is only intended to indicate the success of failure of the procedure itself.


to return data from a stored procedure

you either return a result set (select statement ouput)
or an output parameter..   exec yourproc @parm1,@parm2=@myout output

ps...
  is this really what you are attempting to do ?
  return 1 value from a db query?
     if so then just code it aas a select top 1 *.... style query...


 Cursors within stored procedures are very resource consuming and slow
 and should be avoided ... there are several other techniques that are generally applied to
  avoid them and "loop" around the result set... but in the main you need to think in termms of set
processing rather than doing things in a record/row by record/row manner...


hth
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 5

Expert Comment

by:almander
ID: 33431151
Change the line
RETURN @PermitNumber
to
SELECT @PermitNumber

And I would recommend looking at an alternative to cursors.

0
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 500 total points
ID: 33431169
Unfortunately you can't.
The RETURN from a stored proc isn't meant for what you're trying to do, it can ONLY be an integer.

To do what you want, look at "output parameters" instead:
USE [SQLBurnPermit]
GO
/****** Object:  StoredProcedure [dbo].[GetNexPermitNo]    Script Date: 08/13/2010 11:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetNexPermitNo]
	--**** create the parameter here as "output"
	@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;
	--**** now DON'T declare it in the code...
	--DECLARE @PermitNumber varchar(15);

    -- Insert statements for procedure here
	DECLARE contact_cursor SCROLL CURSOR FOR

	SELECT PermitNo FROM tblPermits 
	ORDER BY PermitNo;
	OPEN contact_cursor;

-- 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;

	--**** now just return normally, the parameter was set in the line above
	RETURN-- @PermitNumber;

	-- SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
	
	
	
	/******** SAMPLE CALL
	declare @pNo varchar(15)
	exec GetNexPermitNo @pNo output
	
	print @pNo
	*/

CLOSE contact_cursor;
DEALLOCATE contact_cursor;

END

Open in new window

0
 

Author Closing Comment

by:DRRobinson
ID: 33431342
Thanks for the details..
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum causes too much time 5 42
Email Notifications for SQL 2005 9 28
SQL Server Count where two id types exist in column 8 27
VB.net and sql server 4 35
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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