September 1, 2004 at 4:05 pm
I need to do something like this but I can't seem to get the params to be set dynamically (works all right when I input params manually in QA)
SELECT *
FROM dbo.fnExtractLinesInCodeSearch(dbo.Codes.PkCode, dbo.Code.Code, 'SomeText', dbo.Codes.StartLine) fnExtractLinesInCodeSearch INNER JOIN
dbo.Codes ON fnExtractLinesInCodeSearch.PkCode = dbo.Codes.PkCode
I get this error :
Server: Msg 170, Level 15, State 1, Line 2
Ligne 2 : syntaxe incorrecte vers '.'.
Is this something that can be done in SQLServer?
CREATE TABLE [Codes] (
[PkCode] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [nvarchar] (4000) COLLATE French_CI_AS NOT NULL ,
[FkObjAccessEvent] [int] NOT NULL ,
[Colid] [int] NOT NULL ,
[SumCheck] AS (checksum([Code])) ,
[CountOfLines] [smallint] NOT NULL ,
[StartLine] [int] NOT NULL ,
CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
(
[PkCode]
) ON [PRIMARY] ,
CONSTRAINT [IX_Codes_FkObjAccessEvent_Colid_Unique] UNIQUE NONCLUSTERED
(
[FkObjAccessEvent],
[Colid]
) ON [PRIMARY] ,
CONSTRAINT [FK_Codes_FkObjAccessEvent] FOREIGN KEY
(
[FkObjAccessEvent]
) REFERENCES [ObjAccessEvents] (
[PkObjAccessEvent]
),
CONSTRAINT [CK_Codes_Code_Not_Empty] CHECK ([Code] <> '')
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[fnExtractLinesInCodeSearch] (@PkCode as int, @Code as nVarChar(4000), @Search as varchar(256), @StartLine as int)
RETURNS @Results TABLE
(
PkItem int identity (1,1) not null primary key,
NoLigne int not null,
LineText nvarchar(1050) not null,
Colid smallint not null,
PkCode int not null
)
AS
BEGIN
Declare @CrLF as char(2)
set @CrLF = char(13) + char(10)
Insert into @Results (NoLigne, LineText, Colid, PkCode) (Select @StartLine + ElementId - 1 as NoLigne, Element as LineText, CHARINDEX (@Search, ELEMENT, 0) as Colid, @PkCode as PkCode from dbo.Split (@Code, @CrLF) where ELEMENT like '%"%' + @Search + '%"%')
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[Split] (@vcDelimitedString varchar(8000),
@vcDelimitervarchar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR:Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1), --Array index
Elementvarchar(1000)--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
September 2, 2004 at 8:17 am
case closed.. found out it's impossible to do something like that.. and for obvious reasons... I don't see how you could inner join on a ever changing recordset.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply