Seems like My stored procedure is returning Integer

debugcn 에 게시 Dev

Lost

I have done my stored procedure 101 recently and just wrote a little stored procedure, which looks like following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectProUserByUsername]
    @userName varchar(300)
AS
BEGIN   
    SET NOCOUNT ON;   
    SELECT * from ProUser
END

For some reason, SSMS has a reason to believe that my return type is integer. It should in object browser that my stored procedure returns integer Return integer

I am not sure what is the reason why it does that. Any ideas?

IMSoP

SQL Server supports both User-Defined Functions and Stored Procedures. Whereas a function takes a number of parameters as input, and returns a single value of a user-defined type as output, a stored procedure is simply a set of connected SQL statements. They can be connected to the rest of the program via input and output parameters, or they can simply modify data, produce result sets, etc, as though the Transact-SQL had been pasted directly into the query.

To quote the Books Online article, a stored procedure can:

Accept input parameters and return multiple values in the form of output parameters to the calling program.
Contain programming statements that perform operations in the database. These include calling other procedures.
Return a status value to a calling program to indicate success or failure (and the reason for failure).

That last point is the sense in which a stored procedure "returns an integer", because the return status is always an integer value (with a default of 0). This is controlled by using the RETURN statement inside the stored procedure.

For instance, if I ran your stored proc above like so:

DECLARE @return_status int;
EXEC @return_status = SelectProUserByUsername 'IMSoP';
SELECT @return_status as status;

I would get an additional result set with status of 0; if I added RETURN 1 at the end of the procedure, I would get 1 instead.

SSMS is simply displaying the "return type" to be consistent with functions, but it's always going to be an integer for a stored procedure.

Note that in some contexts, an integer-returning function and a stored procedure could be used interchangeably; for instance the EXECUTE/EXEC statement in my example above uses the same syntax for any "module" (procedure, function, etc).

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-06-1

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

Seems like My stored procedure is returning Integer

Seems like My stored procedure is returning Integer

Stored Procedure OUT parameter always returning NULL

If an error occurs in my stored procedure, will a cursor local to that procedure get cleaned up?

Two SELECT statements in one stored procedure, one supplying input for another and the other returning more than one row

Stored procedure with conditional insert

Adding a stored procedure to a database

Stored procedure to encrypt data

Stored procedure not working

Simple If Statement in Stored Procedure

Transaction in Stored procedure

Call stored procedure with Repository

Stored Procedure tracking

Nested stored procedure in SQL

Paging order in stored procedure

Cascading DropDownList with Stored Procedure

C++ Function supposed to return Long, returning Integer like value instead

Returning an integer array

Call one stored procedure to another stored procedure in DB2

Stored Procedure Call from Lightswitch

Error compiling Oracle stored procedure

Using Group By in a SqlDataSource with a stored procedure

CONCAT in stored procedure returns null

Stored Procedure not tracking column modifications

Delete statement not working in Stored Procedure

Stored procedure with select where in array

stored procedure to export to CSV with BCP

Not able to create a MySQL stored procedure

Integer returning as NaN when added

Eliminating Duplicate Rows after Modifying Stored Procedure

Date Parameters passed to stored procedure not working as expected