Please HELP!!!User defined function in SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Please HELP!!!User defined function in SQL

  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Please HELP!!!User defined function in SQL

    I'm trying to create a simple function that returns the following:

    If Data.nvarchar3 is null
    then Data.ntext3 = " " and Data.ntext4 = " "


    I need to do this because Sharepoint is is using 2 fields to store both issue and risk descriptions and information and in my report the values that pertain to risks are showing up in the issues fields and vice versa. I want to call it within my SQL statement found below.

    Here is my query. I know where to call it, but just not how to write the function to put a null value into the two fields above if nvarchar3 is null.

    SELECT P.proj_name as ProjectName,Data.uniqueidentifier1,Data.tp_ID, Data.tp_ListId, Data.tp_Modified, Data.tp_Created as 'Created On', Data.nvarchar1 AS Status,
    Data.nvarchar2 AS Category, Data.nvarchar3 AS IssueName, Data.nvarchar4 AS IssuePriority, Data.datetime1 AS DueDate,
    dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext3), '<.*?>', '', 1, 1) AS Discussion, dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext4),
    '<.*?>', '', 1, 1) AS Resolution, Owner.tp_Title AS Owner, ResponsiblePerson.tp_Title AS AssignedTo, Author.tp_Title AS Author,
    Editor.tp_Title AS Editor,
    dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext3), '<.*?>', '', 1, 1) AS RiskDescription, dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext4),
    '<.*?>', '', 1, 1) AS RiskMitigationPlan, dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext5), '<.*?>', '', 1, 1) AS ContingencyPlan,
    Data.nvarchar6 AS RiskName, Data.float1 AS Probability, Data.float2 AS TechnicalImpact,
    Data.float3 AS ScheduledImpact, Data.sql_variant1 AS TechnicalExposure, Data.sql_variant2 AS ScheduledExposure,
    Data.datetime2 AS RetirementDate
    FROM MPSWSSSampleDatabase.dbo.userdata data left outer join
    MPSSampleDatabase.dbo.msp_web_projects P on Data.tp_listid = P.wproj_issue_list_name or Data.tp_listid = P.wproj_risk_list_name LEFT OUTER JOIN
    MPSWSSSampleDatabase.dbo.UserInfo Owner ON Data.tp_SiteId = Owner.tp_SiteID AND Data.int4 = Owner.tp_ID LEFT OUTER JOIN
    MPSWSSSampleDatabase.dbo.UserInfo ResponsiblePerson ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID AND
    Data.int3 = ResponsiblePerson.tp_ID LEFT OUTER JOIN
    MPSWSSSampleDatabase.dbo.UserInfo Author ON Data.tp_SiteId = Author.tp_SiteID AND Data.tp_Author = Author.tp_ID LEFT OUTER JOIN
    MPSWSSSampleDatabase.dbo.UserInfo Editor ON Data.tp_SiteId = Editor.tp_SiteID AND Data.tp_Editor = Editor.tp_ID
    where Data.uniqueidentifier1 is not null

    Thanks for the help in advance!!!!!

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    A function can't modify values in tables, just return data, either as a single value or a table. What you need is most easily achieved with two CASE expressions:

    CASE Data.nvarchar3 WHEN NULL THEN '' ELSE Data.ntext3 END

    and

    CASE Data.nvarchar3 WHEN NULL THEN '' ELSE Data.ntext4 END

    if I understand your need correctly.

    Rune
    If you hit a brick wall, you didn't jump high enough!

Similar Threads

  1. call function for button
    By angela_quests in forum VB Classic
    Replies: 2
    Last Post: 04-13-2007, 05:57 AM
  2. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 01:22 PM
  3. How do I detect an FTP timeout?
    By Julian Milano in forum VB Classic
    Replies: 0
    Last Post: 08-10-2000, 10:16 PM
  4. Please help me -- urgent -- deadlock error
    By chandra in forum VB Classic
    Replies: 0
    Last Post: 06-22-2000, 08:36 AM
  5. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center