TimeToCode TimeToCode - 3 months ago 11
SQL Question

Conversion error when i call a stored procedure from SQL Server in C#

I have a stored procedure called

lastID
like this:

CREATE PROCEDURE lastID(@id varchar(64) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @f VARCHAR(64);

SELECT TOP 1 @f = work_id
FROM workorder
WHERE (RIGHT(work_id,2)) = (RIGHT(Year(getDate()),2))
ORDER BY work_id DESC;

IF(@f IS NULL)
BEGIN
SET @f = 'No work orders';
SET @id = @f;
RETURN @id;
END
ELSE
BEGIN
SET @id = @f;
RETURN @id;
END
END


This stored procedure returns the last id from the table workorder, now I'm trying to execute this procedure in C#, this is the code:

private void lastWorkId()
{
String strConnString = "Server=.\\SQLEXPRESS;Database=recalls;Integrated Security=true";

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "lastID";
cmd.Parameters.Add("@id", SqlDbType.VarChar, 64).Direction = ParameterDirection.Output;
cmd.Connection = con;

try
{
con.Open();
cmd.ExecuteNonQuery();
String id = cmd.Parameters["@id"].Value.ToString();
lastid.Text = id.ToString(); //Putting the return value into a label
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}


I don't know what are wrong with my code, because an exception is displayed, and this says


Conversion failed when converting the varchar value ' OT- 003-16 ' to data type int

Answer

I was wrong about my first answer, here is the updated answer:

Your stored procedure is setup with an OUTPUT parameter of type VARCHAR(64).

Within your proc you have a couple of RETURN @id; statements, which is actually returning a VARCHAR(64). You only need to set your OUTPUT variable within the stored procedure. The RETURN statement expects an integer expression. Here's the updated fixed sproc using OUTPUT appropriately:

ALTER PROCEDURE [dbo].[lastID](@id varchar(64) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @f VARCHAR(64);
SELECT TOP 1 @f = work_id FROM workorder WHERE (RIGHT(work_id,2)) = (RIGHT(Year(getDate()),2)) ORDER BY work_id DESC;
IF(@f IS NULL)
    BEGIN
    SET @f = 'No work orders';
    SET @id = @f;
    END
ELSE
    BEGIN
    SET @id = @f;
    END
END