Is it possible to set a column with a null value to NULL if it is used in a view? (sql server)

Question

To get started, I have two tables, PersonNamesand PersonNameVariations. When searching for a name, it finds the closest name to one of the available ones in PersonNamesand writes it to the table PersonNameVariationsif it does not already exist.

I use the stored proc to search PersonNamesfor the passed in PersonNameVariationand returns information about the found PersonNameand PersonNameVariationthat was matched against it.

Since I'm using the Entity Framework, I need to return a complex type to Import Function, but for some reason, it says that my current environment does not support it. My last option was to use Entityto return to a stored proc instead.

As a result, I will need information about the found PersonNameand recorded PersonNameVariation. Since I cannot return both objects, I created a view PersonSearchVariationInfoand added it to my Entity Framework to use it as a return object.

The problem is that the search does not always return a Person Namematch . It should be able to return only data PersonNameVariationin some cases, which means that all fields in the PersonSearchVariationInforelated ones PersonName must be nullified with zeros .

How can I take my view and make some of the fields nullified? When I do this directly in the Entity Framework, I get a display error:

4 3031: , 1202: . myproject_vw_PersonSearchVariationInfo.DateAdded myproject_vw_PersonSearchVariationInfo nullable entity.     C:\Users\Administrator\Documents\Visual Studio 2010\Projects\MyProject\MyProject.Domain\EntityFramework\MyProjectDBEntities.edmx 1203 15 MyProject.Domain

- ?

,
Matt

Answer 1

.

, ? NULLIF, nullability , . YMMV, .

CREATE TABLE dbo.Foo (ColNonNull varchar(100) NOT NULL)
GO
INSERT dbo.Foo VALUES (NULL) --fails
GO
INSERT dbo.Foo VALUES ('bar') --works
INSERT dbo.Foo VALUES ('') --works
GO
CREATE VIEW dbo.vwFoo
AS
SELECT NULLIF(ColNonNull, '') AS ColNull FROM dbo.Foo
GO
SELECT * FROM dbo.vwFoo
GO
SELECT
    COLUMNPROPERTY(OBJECT_ID('dbo.Foo'), 'ColNonNull', 'AllowsNull') AS TableColNullable,
    COLUMNPROPERTY(OBJECT_ID('dbo.vwFoo'), 'ColNull', 'AllowsNull') AS ViewColNullable
GO

Answer 2

, , .

? NULL, .... .