Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Function Not Working

Answered Function Not Working

  • Monday, January 28, 2013 4:57 PM
     
      Has Code
    Sign In to Vote
    0

    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
========================

    Table2

    DocId  Title      
=========================
5      Disaster       
10     Recovery        
15     Pending 
    CREATE FUNCTION dbo.GetValue @Title NVarchar(Max), INT @DocId)
RETURNS IN
AS 
    SelectT1.AssetId, T1.DocId, T2.Title
    ROM Table1 T1
    JOIN Table2 T2
       ON T1.DocId = T2.DocId
   

    I got a view to work, i just need to get a function working 

     

All Replies

  • Monday, January 28, 2013 5:02 PM
    Moderator
     
     
    Sign In to Vote
    0
    Your function code has several syntax errors right now. What exactly do you want to achieve with this function and do you want a scalar function or table valued function?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, January 28, 2013 5:07 PM
     
     
    Sign In to Vote
    0

    Table 1 is a new table, I am trying to set table 1 Title as a computed column based off of table2 Title column. 

     
  • Monday, January 28, 2013 5:09 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you show the formula for the title? Also, computed column based on another table is not a good idea. It is possible, but it may introduce performance and dependency problems. It is better to keep it as a view.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, January 28, 2013 5:18 PM
     
      Has Code
    Sign In to Vote
    0
     
    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
     
  • Monday, January 28, 2013 5:20 PM
    Moderator
     
     
    Sign In to Vote
    0

    The function code now is OK, but it returns a table therefore it can not be used as a computed column.

    In other words, I do not understand what do you want to achieve now.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, January 28, 2013 5:23 PM
     
     
    Sign In to Vote
    0

    I'm just messing with the code to try and figure it out, 

    I want to make the title of table 2 the title of table 1 ( Say I have AssetId = 1 , DocId = 10,  I want title to be Recovery 

    I want to try how a function works, I have tested a view.
    • Edited by bbt2d Monday, January 28, 2013 5:25 PM
    •  
     
  • Monday, January 28, 2013 5:55 PM
     
      Has Code
    Sign In to Vote
    0

    How about the below code? 

    CREATE TABLE TABLE2
( docid int NOT NULL,
title varchar(50)NOT NULL
)
GO
INSERT INTO TABLE2
VALUES
(5,'Disaster'), 
(10,'Recovery'), 
(15,'Planning')
GO
CREATE TABLE TABLE1
( assetid int NOT NULL,
title varchar(50) NULL,
docid int NOT NULL
)
GO
INSERT INTO TABLE1(assetid, docid)
VALUES 
(1,15), (2,5), (3,10)
GO
UPDATE   t1
SET t1.title = t2.title 
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON  
t1.docid = t2.docid 
Go
SELECT * FROM TABLE1

    Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

     
  • Monday, January 28, 2013 6:04 PM
     
     
    Sign In to Vote
    0

    Well I can update it, I need it to be as a computed column with a function making the changes, when table 2 is changed i want table 1 to be changed also

    • Edited by bbt2d Monday, January 28, 2013 6:13 PM
    •  
     
  • Monday, January 28, 2013 6:26 PM
     
      Has Code
    Sign In to Vote
    0

    Then do (borrowing Hima's code)

    CREATE TABLE TABLE2
( docid int NOT NULL,
title varchar(50)NOT NULL
)
GO
INSERT INTO TABLE2
VALUES
(5,'Disaster'), 
(10,'Recovery'), 
(15,'Planning')
GO
CREATE FUNCTION dbo.ChooseTitle(@docid int) Returns varchar(50) As
Begin
Return (Select title From TABLE2 Where docid = @docid)
End
go

CREATE TABLE TABLE1
( assetid int NOT NULL,
docid int NOT NULL,
title as dbo.ChooseTitle(docid)
)
GO
INSERT INTO TABLE1(assetid, docid)
VALUES 
(1,15), (2,5), (3,10)
GO
SELECT * FROM TABLE1 
Update Table2 Set title = 'Tornado' Where docid = 5;
SELECT * FROM TABLE1
    Tom

     
  • Tuesday, January 29, 2013 4:46 PM
     
     Answered
    Sign In to Vote
    0

    Well I can update it, I need it to be as a computed column with a function making the changes, when table 2 is changed i want table 1 to be changed also

    A good SQL programmer would not even think this way! The VIEW is how we do it in a declarative language. We hate functions and other procedural code. Why do you think this sitnking kludge is a valid way to code?

    I have been teaching SQL for 3 decades and I need to know why people make this kind of huge conceptual error.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL