SQL Server Performance

Error While exceuting the Query

Discussion in 'General Developer Questions' started by prakashuj, Jun 7, 2008.

  1. prakashuj New Member

    Dear All

    CREATE TABLE [Inbox] (
    [GlobalInboxId] [bigint] IDENTITY (1, 1) NOT NULL ,
    [FormCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FormType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SenderId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RecipientId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TimeReceived] [datetime] NULL ,
    [DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FormNameCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    CONSTRAINT [PK_Inbox] PRIMARY KEY CLUSTERED
    (
    [GlobalInboxId]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    The Data will be
    Inbox
    GlobalInboxId FormCode FormType SenderId RecipientId CreatedBy TimeReceived DisplayName FormNameCode
    1 CHR-1 Crequest ai.arc ai.arc ai.arc 20/05/2005 CHRRequest CHR,Issue
    2 Issue-2 Crequest bi.bir bi.bir bi.bir 20/05/2005 CHRRequest CHR,Issue
    3 CHR-2 Crequest ai.arc ai.arc ai.arc 20/05/2005 CHRRequest CHR,Issue
    4 Sup-1 Srequest si.arc si.arc si.arc 20/05/2005 SURequest Sup

    i am using the following funtion

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fnDStringToTable]'))
    DROP FUNCTION [dbo].[fnDStringToTable]
    GO

    --This UDF will split a delimited list into a single column table.

    CREATE FUNCTION dbo.fnDStringToTable
    (
    @list NVARCHAR(4000)
    , @delimiter NCHAR(1) = ',' --Defaults to CSV
    )
    RETURNS
    @tableList TABLE(
    value NVARCHAR(100)
    )
    AS
    BEGIN
    DECLARE @value NVARCHAR(100)
    DECLARE @position INT

    SET @list = LTRIM(RTRIM(@list))+ ','
    SET @position = CHARINDEX(@delimiter, @list, 1)

    IF REPLACE(@list, @delimiter, '') <> ''
    BEGIN
    WHILE @position > 0
    BEGIN
    SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
    IF @value <> ''
    BEGIN
    INSERT INTO @tableList (value)
    VALUES (@value)
    END
    SET @list = RIGHT(@list, LEN(@list) - @position)
    SET @position = CHARINDEX(@delimiter, @list, 1)

    END
    END
    RETURN
    END
    GO


    when i execute following query
    i am getting this error 'Line 2: Incorrect syntax near '.'.'
    select * from Inbox
    inner join dbo.fnDStringToTable(Inbox.FormNameCode, ',') s on s.value=SubString(Formcode,0,CharIndex('-',Formcode))


    Please help me to solve this

  2. techbabu303 New Member

    What happens if you do
    SELECT * from dbo.fnDStringToTable(Inbox.Formcode.name,',') ?
    -SAT
  3. Adriaan New Member

    If you put the ON clause on a new line, does the error move to line nr 3?
  4. techbabu303 New Member

    Any updates ?
    Cheers
    Sat

Share This Page