Question Re: One of the Column returned as NULL from SQL Server 2008 in Product Lifecycle Management Q&A

One of the Column returned as NULL from SQL Server 2008

Former Member — Sat, 07 May 2011 11:27:51 GMT

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

Re: One of the Column returned as NULL from SQL Server 2008

jamie_cawley — Mon, 09 May 2011 13:07:32 GMT

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

Re: One of the Column returned as NULL from SQL Server 2008

Former Member — Mon, 09 May 2011 13:31:22 GMT

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

Re: One of the Column returned as NULL from SQL Server 2008

jamie_cawley — Mon, 09 May 2011 13:39:44 GMT

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

Re: One of the Column returned as NULL from SQL Server 2008

Former Member — Tue, 10 May 2011 04:21:44 GMT

Thanks Jamie