ADO.Net executing Stored Procedure containing dynamic SQL

Question

Post reply

ADO.Net executing Stored Procedure containing dynamic SQL

idridley

Old Hand

Points: 358
More actions
June 22, 2005 at 12:45 am

#167510

I am refactoring a VB6 application to C#/.NET. I am having trouble with ADO.Net returning records from a stored procedure which executes dynamic SQL.
(typical) C# code:
SqlDataReader equipmentReader;
command.CommandText = "[prc select Equipment]";
command.CommandType = CommandType.StoredProcedure;
equipmentReader = command.ExecuteReader(CommandBehavior.Default);
The stored procedure should (and does when run in Query Analyzer) return one record containing 12 fields. The SqlDataReader is aware of the 12 fields but has no rows. The same thing happens if I use a SqlDataAdapter / DataSet to run the stored procedure.
The stored procedure code:
create    procedure [prc select Equipment]
@Case   varchar(50),
@CADClass  varchar(50),
@EquipmentName varchar(50)
as
declare @QueryString varchar(2048)
select @QueryString = [dbo].[fn CAD Export Query String]( @Case, @CADClass, @EquipmentName )
execute( @QueryString )
A typical query string generated by the function is:
select
[Design_Power_Class],
[Name],
[Case],
[Combined_Equipment_Name],
[Volume],
[Tank_Diameter],
[Tank_Height],
[Floor_Slope],
[Roof_type],
[Elevated_Walkway],
[Ladder],
[depth_to_Ground]
from
vwAnaerobic_Digester_Query
where
[Case] = 'COOMBABAH - OPTION B - PROCESS'
and
[Name] = 'BIOGAS_STORAGE_TANK'
Why can't I get any records to in my .NET application? Especially odd when it worked in VB6!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dylan Davis Ten Centuries Points: 1283 More actions · Answer 1

I'm a VB.NET guy myself, but are you doing a

equipmentReader.read()

(or something similar) before you try to read the values of the reader? You may not be into the first row of the reader before you are trying to reference items from it.

Just an idea.

idridley Old Hand Points: 358 More actions · Answer 2

equipmentReader.read() returns false because there are no records. The HasRecords property is false which can be seen from the debugger. Similar result if I populate a DataTable.

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

This might sound a stupid question but where are you setting the parameters of the stored procedure ? There might be a problem with the way they're being passed ?

Dan
www.firstcs.co.uk

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 4

This may not be a problem but have you tried executing an SP that does not have spaces in the name?

I know the [square brackets] should allow spaces to be used, but...

If executing an SP without spaces works then that's a simple identification of the problem, the solution, i.e. renaming everything may not be as simple for you!

Another thing I'd look at is putting quotes around the dynamic SQL in your SP. I didn't expect it to work in QA, but if it does maybe this IS ok?

Lowell SSC Guru Points: 323516 More actions · Answer 5

the proc should probablyo have SET NOCOUNT ON as the first statement; could that be the cause of the issue? i'd rename the proc to have underscores instead of spaces as Bob pointed out...i don't think it is good practice to have object names with spaces.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

idridley Old Hand Points: 358 More actions · Answer 6

I have done some experimenting. The procredure works if I hard code the SQL String but doesn't work if the exact same string is returned from the function.

This metacode will return records to the .NET application:

select @QueryString = 'select [Design_Power_Class], ...'

execute( @QueryString )

This code will not return records to the .NET application:

select @QueryString = [dbo].[fn CAD Export Query String]( @Case, @CADClass, @EquipmentName )

execute( @QueryString )

In both cases, the string stored in @QueryString was identical.

To make matters worse, I wrote a generic stored procedure on the [pubs] database to do a similar thing and it worked!