Multiple queries in SQL server stored procedure is not executing

Question

0.00/5 (No votes)

See more:

I have created a stored procedure in which select,insert,delete and also update queries are there. In which only one query is executing in the stored procedure remaining are not executed.

What I have tried:

Stored Procedure:

CREATE PROCEDURE [dbo].[usp_Emp_LeaveApplication]
(
	@EmpID				VARCHAR(50),
	@NumberOfDays		INT,
	@LeaveFromDate		DATE,
	@LeaveToDate		DATE,
	@CreatedOn			DATETIME,
	@TypeOfLeave		VARCHAR(100),
	@StatusOfLeave		VARCHAR(10),
	@ReasonForLeave		VARCHAR(MAX),
	@Comments			VARCHAR(MAX),
	@StatementType		NVARCHAR(20) = '',
    @ResponseMessage	NVARCHAR(250) OUTPUT
)

AS  
BEGIN

	SET NOCOUNT ON;

	SET @LeaveFromDate = CONVERT(datetime, @LeaveFromDate)
	SET @LeaveToDate = CONVERT(datetime, @LeaveToDate)
	SET @Comments = ISNULL(NULL, @Comments)

	BEGIN TRY
		BEGIN TRANSACTION
			IF (@StatementType = 'Insert')
			BEGIN
				INSERT INTO Emp_LeaveApplication ( EmpID, NumberOfDays, LeaveFromDate, LeaveToDate, CreatedOn, TypeOfLeave, StatusOfLeave, ReasonForLeave, Comments) 
				values( @EmpID, @NumberOfDays, @LeaveFromDate, @LeaveToDate, @CreatedOn, @TypeOfLeave, @StatusOfLeave, @ReasonForLeave, @Comments)

				SET @ResponseMessage = 'Your request for leave has registered successfully!!!'
			END
			ELSE IF (@StatementType = 'Select')
			BEGIN
				select * from Emp_LeaveApplication
			END
			ELSE IF (@StatementType = 'SelectByID')
			BEGIN
				SELECT * FROM Emp_LeaveApplication WHERE EmpID = @EmpID
			END
			ELSE IF (@StatementType = 'Update')
			BEGIN
				UPDATE Emp_LeaveApplication SET
				NumberOfDays = @NumberOfDays, LeaveFromDate = @LeaveFromDate, LeaveToDate = @LeaveToDate, TypeOfLeave= @TypeOfLeave, StatusOfLeave = @StatusOfLeave, ReasonForLeave = @ReasonForLeave WHERE EmpID = @EmpID

				SET @ResponseMessage = 'Your leave status has been updated successfully!!!'
			END  
			ELSE IF (@StatementType = 'Delete')
			BEGIN
				DELETE FROM Emp_LeaveApplication WHERE EmpID = @EmpID

				SET @ResponseMessage = 'Leave request has been deleted'
			END
			ELSE IF (@StatementType = 'Cancel')
			BEGIN
				UPDATE Emp_LeaveApplication SET
				StatusOfLeave = 'Cancelled', ReasonForLeave = @ReasonForLeave WHERE EmpID = @EmpID

				SET @ResponseMessage = 'Your leave status has been cancelled successfully!!!'
			END
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			BEGIN
				SET @ResponseMessage = ERROR_MESSAGE()
				ROLLBACK TRANSACTION
			END
	END CATCH
END

What i am doing wrong. Can anybody explain me

Posted 26-Mar-18 22:37pm

Member 8583441

Updated 27-Mar-18 1:42am

Add a Solution

Comments

Member 8583441 27-Mar-18 4:38am

I am not using any gridview only button click event

OriginalGriff 27-Mar-18 5:22am

Which one is, and what are you passing for the others?
And why would you do that anyway?

Member 8583441 27-Mar-18 5:26am

I need to have some changes in the database tables with particular column..... If it is a wrong one suggest me the better one to practice for myself sir.

2 solutions

Solution 2

Quote:
I need to have some changes in the database tables with particular column..... If it is a wrong one suggest me the better one to practice for myself sir.

The whole idea of a "do everything"SP is wrong: it's just a very bad idea.
You have to call it differently depending on what you are doing: An INSERT or UPDATE woudl use an ExecuteNonQuery or ExecuteScalar, while a SELECT needs a DataAdapter, a DataReader or an ExecuteScalar.
And building a transaction around a SELECT? What use does that serve since an SELECT cannot affect database values?
Even if your query was not a SELECT, the transaction is pretty much useless as you only try to affect a single row at a time, and if that fails the system won't change anything anyway!

To be honest, I wouldn't put any of that in an SP - there is nothing there that isn't clearer if handled directly in the presentation language.

Posted 27-Mar-18 1:43am

OriginalGriff

Comments

#realJSOP 27-Mar-18 10:07am

I personally prefer to use stored procs rather than put all that sql into the code in the app. I even have a way to document stored procs on the C# side, so you know what the parameters are via intellisense. Sure, it makes a little more work, but it's worth it in the end (for me).

Member 8583441 28-Mar-18 1:41am

Thank you all for your suggestions. Basically I am having 1 year of experience so i doesn't know how to write the code. I am learning person in coding

OriginalGriff 28-Mar-18 4:34am

You're welcome!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2018-03-26T23:37:00

Your current approach is a false optimization, and a maintenance headache. You can indeed insert/update in one stored proc, and even without passing a parameter in order to pull it off. It would go something like this:

SQL

create procedure sp_UpsertData
(
    @field1 type
    @field2 type
    @field3 type
    @field4 type
    @field5 type
)
as begin
    -- update the appropriate record
    UPDATE [mytable] 
    SET field1=@field1
        field2=@field2
        field3=@field3
    WHERE field4 = @field4 and field5 = @field5
    -- if no records were affected
    IF @@ROWCOUNT = 0
    begin
        -- insert the data
        INSERT INTO [mytable] (field1, field2, field3, field4, field5)
        VALUES (@field1, @field2, @field3, @field4, @field5)
    end
end

As for the Delete or Select operations,you can group the select stuff without much push-back from DBAs, but the delete action really should be in its own stored proc as well.

Also, look into default parameters, where a parameter is set to a default value, and use that as an indication as to what type of select to perform.