Need to retrieve some data from SQL as well as insert with out using out parameter

Question

0.00/5 (No votes)

See more:

Hi,

I have a stored procedure which will insert some data into a table, and retrieve back some data in sql. In front end using c#, i need to fetch back the result set from my procedure and need to use it.

Which statement i need to use? I found that executereader() is the solution, but this is not working. I should not use the out parameter in sql as well as c# as per company coding standards. Can anybody explain this with an example?

stored procedure

create procedure name
@patientId
as
begin
insert into table values(a,b,c);
select * from table where value = a;
end

in C#

var reader = cp.executereader();
var a=reader["value"].tostring();

This is the way i have implemented my functionality(representational only).

What I have tried:

create procedure name
@patientId
as
begin
insert into table values(a,b,c);
select * from table where value = a;
end

in C#

var reader = cp.executereader();
var a=reader["value"].tostring();

This is the way i have implemented my functionality(representational only).

Posted 13-Aug-17 20:49pm

Member 10945065

Updated 13-Aug-17 23:52pm

Afzaal Ahmad Zeeshan

v2

Add a Solution

Comments

F-ES Sitecore 14-Aug-17 4:43am

Your SP is generating two result sets. The INSERT is creating one (even though it doesn't retrieve data it will still tell you how many rows were inserted and that generates a result set), and the the SELECT is creating one (that contains the data you want). Your ExecuteReader is then reading the first set, the INSERT, so sees no data. The easiest way to handle this is to stop the INSERT generating a result set by enabling "NOCOUNT"

create procedure name
@patientId
as
begin

SET NOCOUNT ON
insert into table values(a,b,c);
select * from table where value = a;
end

Member 10945065 16-Aug-17 2:43am

Thanks for your reply..Hope this works..Let me check by implementing this.

1 solution

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)

Mycroft Holmes · Answer 1 · 2017-08-13T22:41:00

Presuming you have made a valid connection to the database and have successfully created the record in the table.

After you have inserted the record you can select that record and it will be returned in the data reader.

SQL

Insert TableName
(Field1,Field2)
Value (@Value1, Value2)

SET @ID = Scope_Identity() -- gets the last ID from and identity field

Select * From TableName where ID = @ID

This is really basic stuff you can get from any article on CRUD - do some research.

BTW you stored procedure as written above makes absolutely no sense and does nothing you described.