FillSchema and stored procedure IF statements
- From: Caitlin <cshaw@xxxxxxxxxxxxxxxx>
- Date: Tue, 30 Oct 2007 16:22:57 -0000
I'm trying to use FillSchema to get the results schema for a stored
procedure that, simplified, does this:
CREATE PROCEDURE SomeProc
@Flag bit
AS
IF @Flag = 1
SELECT 'foo' = 'bar'
SELECT TOP 5 'baz' = snafu FROM SomeTable
GO
If I then call
objSQLCommand.CommandType = CommandType.StoredProcedure
objSQLCommand.CommandText = "SomeProc"
objDataAdapter.MissingSchemaAction = MissingSchemaAction.Add
objDataAdapter.SelectCommand = objSQLCommand
objDataAdapter.FillSchema(objDataSet, SchemaType.Source)
objDataAdapter.Fill(objDataSet)
with @Flag = 1, I get the correct dataset back: two tables, the first
with one column called 'foo' and one row, the second with one column
called 'baz' and 5 rows. However, if I call this code with @Flag = 0,
what I get back are two tables, the first with two columns called
'foo' and 'baz' and 5 rows, and the second with one column called
'baz' and no rows at all.
This seems to be because the FillSchema goes down into the IF @Flag
branch in my stored procedure for schema information regardless of the
value of @Flag and that then the DataAdapter.Fill is putting its one
table of data into the first available table.
My question is, what other options do I have for getting the schema
information here? Can I change this behavior of FillSchema's? In
this particular instance I could skip the FillSchema step entirely if
I know that @Flag <> 1, but I'd really prefer a more general solution
that would allow me to put more branches in my stored procedure if
they're needed someday.
Thanks,
Caitlin Shaw
.
- Prev by Date: RE: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- Next by Date: Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- Previous by thread: I set my database to trustworthy and deployed this clr stored proc as unsafe ok but when i run this it returns 1 each time. as if ival keeps getting set back to 0 each time i call this clr stored proc. How to fix so that ival keeps its current value each t
- Next by thread: is there anything like XmlDocument.ImportNode but for SQL Server XQuery?
- Index(es):
Relevant Pages
|
Loading
