SQL problem: same column in one

hy! I have 2 tables and in each I have a colu开发者_开发百科mn date, I need to make a single table with the information from all 2 tables with a column date which i want to get from the 3 tables,but in the same column i tried the following code, but didn`t work

CREATE FUNCTION dbo.GetContactInformation(@id int)
RETURNS @retActivityInformation TABLE 
(
ClientID int NOT NULL,
ActivityDate datetime NULL,
Tipe nvarchar(50) NULL,
Number nvarchar(50) NULL,
Value int NULL,
Statu nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (clientID ASC)

) AS 
BEGIN
    DECLARE 
        @ClientID int,
        @ActivityDate datetime, 
        @Tip nvarchar(50), 
        @Number nvarchar(50),
        @Value int,
        @Statu nvarchar(50);
       SELECT 
        @ClientID = ClientID, 
        @ActivityDate = ActivityDate, 
        @Number = Number,
        @Value = Value,
        @Statu = Statu
    FROM Fa,Pay 
    WHERE ID = @id;
    SET @ActivityDate = 
        CASE 
            WHEN EXISTS(SELECT Fa.DataEmitere FROM Fa AS e 
                WHERE e.ID = @id) 
                THEN 'Fa'           
            WHEN EXISTS(SELECT Pay.Data FROM Pay AS bec
                WHERE bec.ID = @id) 
                THEN 'Pay'            
        END;

    IF @id IS NOT NULL 
    BEGIN
        INSERT @retActivityInformation
        SELECT @clientID, @ActivityDate,  @Number, @Value,@Statu;
    END;

    RETURN;
END;

Just prefix the field with the database name. I am going to assume the date you actually mean is ActivityDate. If you want to SELECT/INSERT using this field you will need to prefix with Fa or Pay so it would be Fa.ActivityDate or Pay.ActivityDate.

If this is not the field then we'd need more info.

Use the column by specifying the table name as below:-

CREATE FUNCTION dbo.GetContactInformation(@id int)
RETURNS @retActivityInformation TABLE 
(
ClientID int NOT NULL,
ActivityDate datetime NULL,
Tipe nvarchar(50) NULL,
Number nvarchar(50) NULL,
Value int NULL,
Statu nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (clientID ASC)

) AS 
BEGIN
    DECLARE 
        @ClientID int,
        @ActivityDate datetime, 
        @Tip nvarchar(50), 
        @Number nvarchar(50),
        @Value int,
        @Statu nvarchar(50);
       SELECT 
        @ClientID = ClientID, 
        @ActivityDate = Fa.ActivityDate, 
        @Number = Number,
        @Value = Value,
        @Statu = Statu
    FROM Fa,Pay 
    WHERE ID = @id;
    SET @ActivityDate = 
        CASE 
            WHEN EXISTS(SELECT Fa.DataEmitere FROM Fa AS e 
                WHERE e.ID = @id) 
                THEN 'Fa'           
            WHEN EXISTS(SELECT Pay.Data FROM Pay AS bec
                WHERE bec.ID = @id) 
                THEN 'Pay'            
        END;

    IF @id IS NOT NULL 
    BEGIN
        INSERT @retActivityInformation
        SELECT @clientID, @ActivityDate,  @Number, @Value,@Statu;
    END;

    RETURN;
END;

See the middle part here:

CREATE FUNCTION dbo.GetContactInformation(@id int)
RETURNS @retActivityInformation TABLE 
(
ClientID int NOT NULL,
ActivityDate datetime NULL,
Tipe nvarchar(50) NULL,
Number nvarchar(50) NULL,
Value int NULL,
Statu nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (clientID ASC)

) AS 
BEGIN
    DECLARE 
        @ClientID int,
        @ActivityDate datetime, 
        @Tip nvarchar(50), 
        @Number nvarchar(50),
        @Value int,
        @Statu nvarchar(50);
       SELECT 
        @ClientID = ClientID, 
        @ActivityDate = ActivityDate, 
        @Number = Number,
        @Value = Value,
        @Statu = Statu
    FROM Fa,Pay 
    WHERE ID = @id;
    SET @ActivityDate = ISNULL(
        (SELECT top 1 Fa.DataEmitere FROM Fa AS e WHERE e.ID = @id),
        (SELECT top 1 Pay.Data FROM Pay AS bec WHERE bec.ID = @id))

    IF @id IS NOT NULL 
    BEGIN
        INSERT @retActivityInformation
        SELECT @clientID, @ActivityDate,  @Number, @Value,@Statu;
    END;

    RETURN;
END;

Essentially, instead of testing to see if the data EXISTS just to get the field name, get the data directly.