Stored procedure invoked in C# doesn't return any records

Question

I'm struggling with very strange thing. I'm having SQL Server Stored procedure which has XML and Guid as input parameters.

@ID uniqueidentifier,
@Xml XML

it's then creating a table from XML, which is as the end filled with XML data. In the next step SQL cursor is created and it's getting through the whole table created previously.

The fact is that this stored procedure invoked in SQL Server is working fine and returning as many records as it should.

The problem comes when I'm connecting to this stored procedure in C#. I'm creating XML that is an input parameter:

StringWriter writer = new System.IO.StringWriter();
table.WriteXml(writer, false);

new SQLParameterClass("@Xml", SqlDbType.Xml, 4000, ParameterDirection.Input, writer.ToString())

whole code works fine (there's no errors / exceptions) but the problem is it's not returning any records as it should be.

I really don't know what is causing this issue so I really appreciate some help at this aspect.

Thanks!

Answer 1

You need to pass output parameter to SP to return the output. I am supposing that u r returning integer value

var output= new SqlParameter { ParameterName = "@output", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int };

Assign value in Sp and returning from stores procedure will assign value to output

Check this link for more http://msdn.microsoft.com/en-us/library/59x02y99%28v=vs.110%29.aspx