How to return two output values from SQL stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I want to get two Output values at the same time from the sql stored procedure .I am using visual studio 2005 and sql server 2005.In my stored proceudre there was no problem it returns Two Values.

@EmpSid   @RevisionNo 
     79  	2

What I have tried:

My code is here

MySID = objCmd.Parameters.AddWithValue("@EmpSid", 0)
     RevisionNo = objCmd.Parameters.AddWithValue("@RevisionNo", 1)
     objCmd.Parameters.Item("@EmpSid").Direction = ParameterDirection.Output
     objCmd.Parameters.Item("@RevisionNo").Direction = ParameterDirection.Output
objCmd.ExecuteNonQuery()

Sql Procedure

ELSE IF @Mode='EDIT'  
BEGIN  
 Select @RevisionNo = RevisionNo+1 from tblEmployeeMaster  where SID=@EmpSid 

UPDATE tblEmployeeMaster set CompanySID=@CompanySID, EmpCode=@Empcode,HonorficsSID=@HonorficsSID,EmpName=@EmpName,  
 Dob=@Dob, MaritalStatusSID=@MaritalStatusSID, GenderSID=@GenderSID, BloodGroup=@BloodGroup,  
 DateOfJoin=@DateOfJoin,EmpPhoto=@EmpPhoto ,Notes=@Notes,RevisionNo=@RevisionNo, CreatedBy=@CreatedBy, CreatedOn=GETDATE(),
 EditedBy=@CreatedBy,EditedOn=GETDATE() WHERE SID=@SID  
   SET @EmpSid=@SID
END

It shows an error

Cannot insert the value NULL into column 'RevisionNo', table 'JSolutionHR.dbo.tblEmployeeMaster'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Posted 18-Sep-18 19:29pm

Flower@12

Updated 20-Sep-18 3:58am

v5

Add a Solution

Comments

jaket-cp 19-Sep-18 5:41am

Looking at the error message - put another way - it says attempting to insert null into
JSolutionHR.dbo.tblEmployeeMaster.RevisionNo

Check to see what you are getting for
Select @RevisionNo = RevisionNo+1 from tblEmployeeMaster where SID=@EmpSid

Assuming it is getting to this part of the query

2 solutions

Solution 2

SQL

Select ... from tblEmployeeMaster where SID = @EmpSid
UPDATE tblEmployeeMaster ... WHERE SID = @SID
SET @EmpSid = @SID

That first WHERE clause should be using @SID, not @EmpSid:

SQL

SELECT @RevisionNo = RevisionNo + 1 FROM tblEmployeeMaster WHERE SID = @SID

Posted 20-Sep-18 3:58am

Richard Deeming

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)

ZurdoDev · Accepted Answer · 2018-09-19T03:00:00

Solution 1

As mentioned in the comments, @RevisionNo is null. The error has nothing to do with multiple output parameters. The error should be pretty clear. You are trying to put null into a column that does not accept null.

Posted 19-Sep-18 3:00am

ZurdoDev