Function Not Working

  • How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title in table 1 a computed column from table2 based on docId=DocId. I keep trying to add it as computed column in SSMS and it wont let me.

    Table1

    AssetId Title DocId

    1 10

    ========================

    Table2

    DocId Title

    =========================

    5 Disaster

    10 Recovery

    15 Pending

    CREATE FUNCTION dbo.GetValue (@DocId INT)

    RETURNS TABLE

    AS

    RETURN

    (SELECT T1.AssetId, T1.DocId, T2.Title

    FROM Table1 T1

    JOIN Table2 T2

    ON T1.DocId = T2.DocId

    WHERE T1.DocID = @DocId

    )

    GO

    --I was messing with this code, but its not working, I want to be able to make Table1 Title a computed column based on Table 2 Title column and documentId

    ALTER TABLE dbo.Table1

    ADD Title AS dbo.GetValue(Title)

    I get errors.

  • The way you have your GetValue function built, you need to put the DocID in as your arguement in the function, not the title.

  • mail4bbt (1/28/2013)


    How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title in table 1 a computed column from table2 based on docId=DocId. I keep trying to add it as computed column in SSMS and it wont let me.

    Table1

    AssetId Title DocId

    1 10

    ========================

    Table2

    DocId Title

    =========================

    5 Disaster

    10 Recovery

    15 Pending

    CREATE FUNCTION dbo.GetValue (@DocId INT)

    RETURNS TABLE

    AS

    RETURN

    (SELECT T1.AssetId, T1.DocId, T2.Title

    FROM Table1 T1

    JOIN Table2 T2

    ON T1.DocId = T2.DocId

    WHERE T1.DocID = @DocId

    )

    GO

    --I was messing with this code, but its not working, I want to be able to make Table1 Title a computed column based on Table 2 Title column and documentId

    ALTER TABLE dbo.Table1

    ADD Title AS dbo.GetValue(Title)

    I get errors.

    It seems you are new around here. Welcome!!!

    Posting things like "I get errors." is really not very helpful. What are the errors?

    I see far more wrong what you have here than roryp suggested. You do in fact have a datatype mismatch but there are far more serious issues with the design of what you are doing. Your function returns a table with 3 columns and you are trying to put that into a single column. This is never going to work like that.

    What are you trying to do here?

    You should probably take a look at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ok, Thanks, I new here and to sql.

    I am trying to make Title in table 1 a computed column that will get updated every time the title column in table 2 gets updated.

  • mail4bbt (1/28/2013)


    Ok, Thanks, I new here and to sql.

    I am trying to make Title in table 1 a computed column that will get updated every time the title column in table 2 gets updated.

    We can help but you have to first help us. Please read the link I previously suggested. Without something to work with there is little chance you will get correct answers.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • CREATE TABLE [dbo].[Asset](

    [AssetId] [int] NOT NULL,

    [DocId] [int] NULL,

    [Title] [nvarchar](max) NULL,

    CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED

    (

    [AssetId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Doc](

    [DocId] [int] NOT NULL,

    [Title] [nvarchar](max) NULL,

    CONSTRAINT [PK_Doc] PRIMARY KEY CLUSTERED

    (

    [DocId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    INSERT INTO [Doc]([Title])

    VALUES('Recovery');

    INSERT INTO [Doc]([Title])

    VALUES('Disaster');

    INSERT INTO [Doc]([Title])

    VALUES('Pending');

    INSERT INTO [Doc]([Title])

    VALUES(NULL);

    INSERT INTO [Asset]([DocId], [Title])

    VALUES(2, NULL);

  • I put sample values in the asset table just to see what I meant

  • The sample you posted won't work because there is no DocId specified on the inserts into Doc.

    I need to understand the relationship between these two tables. The way you have them defined is denormalized. Do you have an Asset that can have any number of Docs? Is that what you are trying to do here?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply