Null value being returned from a Stored Procedure using Linq to SQL

Question

If I run a stored procedure in my database (SQL 2000) and return the SCOPE_IDENTITY, I receive a value. However, using LINQ to SQL, I get a null value for the Column1 property. Anyone have a suggestion?

Answer 1

Make sure you have only one SELECT statement at the end of your SP. So, something like this:

IF something
DO something
   SET variables
ELSE
   SET variables

SELECT variables

Also, rename your column in the select statement and possibly cast it into proper data type. Something like this:

SELECT CAST(@variable AS DataType) AS [ColumnName];

I ran into this a week ago because I had 2 SELECT statements in a SP and they were screwing each other up. The SP always did what it was supposed to but it always sent null back. Since I switched to what I wrote above it's worked like a charm.

Hope this helps you