locked
C# - Stored Procedure with Output throws two SqlException different errors when attempting to fix RRS feed

  • Question

  • User236248667 posted

    The language I'm using is C#, and I'm using SQL Server 2014.

    The SqlException error messages I'm getting are:

    • Procedure or function CreateThread has too many arguments specified.
    • Procedure or function 'GetNewThreadID' expects parameter '@output', which was not supplied.

    The first error message is thrown when I specified @output variable in the SqlCommand parameters. The second error message is thrown when I removed the specified @output variable.

    I've tried using the SQL Server 2014 Profiler, and got the following SQL code to trigger the SqlException errors:

    exec CreateThread @newTitle='dsf',@newPostTitle=default,@newBody='',@newAuthor='test_author'

    Frankly, I'm very confused about this, and thus to seek help from the community. If anyone knows what I need to fix, please let me know. Thanks.

    This is the following code for my SQL codes:

    CREATE PROCEDURE CreateThread 
    	-- Add the parameters for the stored procedure here
    	@newTitle varchar(200) = '',
    	@newPostTitle varchar(200) = null,
    	@newBody varchar(8000) = '',
    	@newAuthor varchar(200) = ''
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	--Generate a new thread ID.
    	declare @newThreadID int;
    	exec GetNewThreadID @title = @newTitle, @output = @newThreadID output;
    	print 'New Thread ID: ' + CONVERT(varchar(100), @newThreadID);
    
    	--Generate a new post number.
    	declare @newPostNumber int;
    	exec @newPostNumber = GetPostNumber @threadTitle = @newTitle, @threadID = @newThreadID;
    	print 'New Post Number: ' + CONVERT(varchar(100), @newPostNumber);
    
    	--Inserting new threads.
    	insert into asp_practice.dbo.Threads (ThreadID, Checksums, ThreadTitle, PostTitle, Body, CreationDate, Author, PostNumber)
    	values (@newThreadID, CHECKSUM(@newTitle, @newThreadID), @newTitle, @newPostTitle, @newBody, CONVERT(datetime2, GETDATE()), @newAuthor, @newPostNumber);
    END
    GO
    
    
    
    
    
    CREATE PROCEDURE GetNewThreadID
    	-- Parameters
    	@title varchar(200) = '',
    	@output int output
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	declare @result int = 1;
    	declare @resultCount int;
    	select top 1 @result = a.ThreadID from asp_practice.dbo.Threads as a where a.ThreadTitle = @title and a.PostNumber = 1 order by a.ThreadID desc;
    	select @resultCount = COUNT(a.ThreadID) from asp_practice.dbo.Threads as a where a.ThreadTitle = @title and a.PostNumber = 1;
    	if (@result is null)
    		select @result = MAX(a.ThreadID) from asp_practice.dbo.Threads as a;
    	else if (@resultCount > 0)
    		select @result = @result + 1;
    	select @output = @result;
    	select @result;
    	return @result;
    END
    go

    And this is my C# code:

    [WebMethod]
    public void CreateThread (ForumThread data)
    {
    	using (SqlCommand command = new SqlCommand ()) {
    		using (SqlConnection connection = new SqlConnection (ForumsService.CONNECTIONSTRING)) {
    			if (connection.State != ConnectionState.Open)
    				connection.Open ();
    			command.Connection = connection;
    			command.CommandType = CommandType.StoredProcedure;
    			command.CommandText = "CreateThread";
    
    			command.Parameters.Add ("@newTitle", SqlDbType.VarChar).Value = data.Title;
    			command.Parameters.Add ("@newPostTitle", SqlDbType.VarChar).Value = data.PostTitle;
    			command.Parameters.Add ("@newBody", SqlDbType.VarChar).Value = data.Body;
    			command.Parameters.Add ("@newAuthor", SqlDbType.VarChar).Value = data.Poster;
    
    			command.ExecuteNonQuery ();
    			command.Parameters.Clear ();
    
    			connection.Close ();
    		}
    	}
    }
    
    [WebMethod]
    public int GetNewThreadID (string title)
    {
    	//This is the very first thread, so the thread id is 1.
    	int result = 1;
    
    	using (SqlConnection connection = new SqlConnection (ForumsService.CONNECTIONSTRING)) {
    		using (SqlCommand command = new SqlCommand ()) {
    			if (connection.State != ConnectionState.Open)
    				connection.Open ();
    			command.Connection = connection;
    			command.CommandType = CommandType.StoredProcedure;
    			command.CommandText = "GetNewThreadID";
    			command.Parameters.Add ("@title", SqlDbType.VarChar).Value = title;
    
    			command.Parameters.Add ("@output", SqlDbType.Int);
    			command.Parameters ["@output"].Direction = ParameterDirection.Output;
    
    			using (SqlDataReader reader = command.ExecuteReader ()) {
    				if (reader.HasRows) {
    					try {
    						reader.Read ();
    						Debug.WriteLine ("Reader result: " + reader.GetInt32 (0));
    						Debug.WriteLine ("Reader output: " + command.Parameters ["@output"].Value);
    					}
    					catch (Exception e) {
    						Debug.WriteLine ("Error (GetNewThreadID) - " + e.Message);
    					}
    				}
    			}
    			//result = (int) command.ExecuteScalar();
    			command.Parameters.Clear ();
    
    			connection.Close ();
    		}
    	}
    	return result;
    }

    If anyone wants to try testing it out, here's the SQL code for creating the table that I'm using to test the aforementioned SQL codes above:

    create table Threads (
    	_databaseKey int primary key identity(1,1) not null,
    	ThreadID int not null,
    	Checksums int not null,
    	ThreadTitle varchar(200) not null,
    	PostTitle varchar(200),
    	Body varchar(max) not null,
    	CreationDate datetime2 not null,
    	Author varchar(200) not null,
    	PostNumber int not null,
    );
    

    Thursday, March 2, 2017 6:35 PM

All replies

  • User-2057865890 posted

    Hi Tom_Befrugal,

    The first error message tells you that the SQL procedure expect a number of parameters and that the calling program gives a different number of parameters. Because you didn't post GetPostNumber Procedure, so I changed the above code set @newPostNumber=1

    --Generate a new post number.
    declare @newPostNumber int;
    set @newPostNumber=1
    --exec @newPostNumber = GetPostNumber @threadTitle = @newTitle, @threadID = @newThreadID;
    --print 'New Post Number: ' + CONVERT(varchar(100), @newPostNumber);

    and tried, it works on my side.

    Best Regards,

    Chris

    Wednesday, March 8, 2017 7:11 AM
  • User236248667 posted

    Which SQL procedure are you referring to? It has to do with the calling program... I see.

    Because the error messages are focused on CreateThread and GetNewThreadID sprocs, I thought it was unnecessary to put up GetPostNumber sproc. Embarassed Should've posted on there because I did actually use that sproc in CreateThread, in hindsight... The GetPostNumber sproc is just basically fetching the referenced thread's post number data, and not much else.

    I'm guessing I should follow where the SQL procedures are being called from, and start working my way towards the actual issue.

    Wednesday, March 8, 2017 1:31 PM
  • User-2057865890 posted

    Hi Tom_Befrugal,

    To troubleshoot the issue, you could use TRY…CATCH RAISERROR to return Errors and Warnings from a SQL Server Stored Procedure in ADO.NET.

    reference:

    https://msdn.microsoft.com/en-us/library/ms178592.aspx

    https://support.microsoft.com/en-us/help/321903/how-to-return-errors-and-warnings-from-a-sql-server-stored-procedure-in-ado.net

    Best Regards,

    Chris

    Tuesday, March 14, 2017 6:06 AM
  • User236248667 posted

    I'll try that then. 

    If that all fails, then I'll rewrite the whole code in the store procedure, as well as the code in the ASPX code-behind file, so it is more consistent.

    I need someone to recommend which post to mark as answer. 

    Tuesday, March 14, 2017 3:36 PM