One of the Column returned as NULL from SQL Server 2008

[Former Member]

Hi,

I have written a stored procedure in SQL server 2008. The procedure reads data into a temporary table @MeterWiseConsumptionBetwDates as shown

SELECT @sqlstatement = 'SELECT '''@MeterName''', CAST ( MAX('@Meterfield') AS FLOAT )-CAST ( MIN('@Meterfield') AS FLOAT )

FROM KWH

WHERE KWH.timestamp BETWEEN @StDate AND @EndDate'

INSERT INTO @MeterWiseConsumptionBetwDates(MeterName,Consumption)

EXEC sp_executesql @sqlstatement, N'@StDate datetime, @EndDate datetime', @StDate, @EndDate

Now when I execute the stored procedure in SQL Server Management Studio it returns all the columns from the table @MeterWiseConsumptionBetwDates, but when I execute the procedure in MII it returns the values in the Consumption column as null. The MeterName column is returned correctly in both the cases.

Please help.

Thanks

[jamie_cawley]

What is the syntax you are using to call the stored procedure from MII, I would assume its a problem with your date fields.

Jamie

[Former Member]

Thanks

I have tried with both

execute <proc name> 'MM/dd/yyyy HH:mm:ss'

and

execute <proc name> to_date('MM/dd/yyyy HH:mm:ss')

But strangely today i added another column to the query. The difference in date in the first date column is a day and in the second its from the start of the month to now. And it has started returning both the columns.

Edited by: maaz ahmed on May 9, 2011 3:31 PM

[jamie_cawley]

Glad to hear that it is working. You should be able to use

execute <proc name> '[SD]'

[SD] and [ED]

are the query date tokens, so whatever you have assigned on the Date Range tab will be passed to the query.

Jamie

Edited by: Jamie Cawley on May 9, 2011 9:40 AM

[Former Member]

Thanks Jamie