i am trying to run a sql server 2008 stored procedure but recieving error
Procedure or function PopAllotedEmail has too many arguments specified.
c# code:
public static void PopAllotedEmail(SqlConnection con, SqlTransaction trans, int jobId,
out int subscriberId, out string email, out int validationCode)
{
using (SqlCommand cmd = new SqlCommand("[dbo].[PopAllotedEmail]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@jobId", SqlDbType.Int).Value = jobId;
cmd.Parameters.Add("@subscriberId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@validationCode", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
cmd.Parameters["@subscriberId"].IsNullable = true;
cmd.Parameters["@email"].IsNullable = true;
cmd.Parameters["@validationCode"].IsNullable = true;
if (trans != null)
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
if (cmd.Parameters["@subscriberId"].Value == DBNull.Value)
subscriberId = -1;
else
subscriberId = Convert.ToInt32(cmd.Parameters["@subscriberId"].Value);
if (cmd.Parameters["@email"].Value == DBNull.Value)
email = null;
else
email = Convert.ToString(cmd.Parameters["@email"].Value);
if (cmd.Parameters["@validationCode"].Value == DBNull.Value)
validationCode = -1;
else
validationCode = Convert.ToInt32(cmd.Parameters["@validationCode"].Value);
int retVal = Convert.ToInt32(cmd.Parameters["ReturnValue"].Value);
if (retVal != 0)
throw new Exception("Could not pop email.");
}
}
sql procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id =
OBJECT_ID(N'[dbo].[PopAllotedEmail]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[PopAllotedEmail]
(
@jobId int,
@email varchar(50) = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @subscriberId int
SELECT TOP 1 @subscriberId =
s.[SubscriberID], @email = s.[Email]
FROM [Subscribers] s INNER JOIN
[JobEmails] je ON s.[SubscriberID] =
je.[SubscriberID] WHERE je.[JobID] =
@jobId ORDER BY s.[SubscriberID] ASC
IF @@ROWCOUNT = 0
RETURN 0
BEGIN TRAN -- Transaction starts
DELETE [JobEmails] WHERE [JobID] = @jobId AND
[SubscriberID] = @subscriberId
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
UPDATE [Jobs] SET [EmailsSent] =
[EmailsSent] + 1
WHERE [JobID] = @jobId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN -2
END
COMMIT TRAN
RETURN 0
END'
END
GO