I recently made a Stored procedure on Azure for my webapplication. I use ASP.net core 2.0 to make it. In the stored procedure I want to get a specific PC component type for example: "Motherboard". After that I want to return that type to my application.
This is my stored procedure
CREATE PROCEDURE GetCurrentType
@BuildID INT = 1,
@t NVARCHAR(128) OUTPUT,
@Index INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Index = COUNT(*)
FROM Partslist
WHERE buildId = @BuildID;
SELECT @t = _Type
FROM ComponentTypes
WHERE PriorityID = @Index;
RETURN @t
END
The stored procedure counts all the already selected parts that the user has picked. With that number it looks for the next type in the table ComponentTypes for the user to pick from. This type will be returned. The BuildID needs to be 1 for now but later I will change it to be a actual parameter.
C# code:
IEnumerable<string> test = db.Query<string>
(
"GetCurrentType",
new {BuildID = 1, t = "", Index = 0},
commandType: CommandType.StoredProcedure
);
This should get the type and set it in the test IEnumerable. But when I run it it gets the following error:
System.Data.SqlClient.SqlException: 'Conversion failed when converting the nvarchar value 'Memory' to data type int.'
I also tried to use a DynamicParameters but that just gave me the same error. The really weird thing is that I don't have a Memory table or column in my database.
@t NVARCHAR(128) OUTPUT,
and
RETURN @t
The RETURN
value is always numeric; from RETURN
:
Syntax
RETURN [ integer_expression ]
Arguments
integer_expression
Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.
Return Types
Optionally returns int.
Basically: don't RETURN
this. Either SELECT
it, or use an OUTPUT
parameter.
My recommendation would be SELECT
.