Hello,
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code Snippet
SELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet
exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet
CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@.QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @.v_List_ITEM as varchar(8000)
set @.v_List_ITEM=''
if @.QUERY is not null
Begin
declare @.cur_Lect_ITEM CURSOR;
declare @.FUNCTION AS NVARCHAR(4000);
Declare @.ITEM as VARCHAR(255);
SET @.FUNCTION = 'set @.mainCursor=cursor for ' + @.QUERY + ' for read only open @.mainCursor'
EXEC sp_executesql @.FUNCTION,N'@.mainCursor cursor output', @.cur_Lect_ITEM output
fetch next from @.cur_Lect_ITEM into @.ITEM
while @.@.fetch_status=0
begin
set @.v_List_ITEM=@.ITEM + ' ; ' + @.v_List_ITEM
fetch next from @.cur_Lect_ITEM into @.ITEM
end
deallocate @.cur_Lect_ITEM
SET @.v_List_ITEM=REPLACE(REPLACE(@.v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @.v_List_ITEM=left(@.v_List_ITEM,len(@.v_List_ITEM)-3)
End
RETURN @.v_List_ITEM
END
Can you help me please?
Thank you
You DO NOT execute a FUNCTION.
You use a FUNCTION inline (like an expression), or
you use a FUNCTION like a table.
Perhaps your 'function' should be a Stored Procedure...
|||1. You can't use dynamic SQL in Functions
2. You needn't this much complex query to concatinate the items
If you use sql server 2005 the following query will work for you..
Code Snippet
SELECT
APPLI_SUPPLIER.N_SUPPLIER_ID,
APPLI_SUPPLIER.V_SUPPLIER_LABEL,
(SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) + ';' as [text()]
FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL
ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)
AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)
AND APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_CONSTRUCTION.N_SUPPLIER_ID )
for XML path('')) AS V_PROCESS_ITEMS
FROM
APPLI_SUPPLIER APPLI_SUPPLIER_MAIN
INNER JOIN APPLI_SUPPLIER_SKILL APPLI_SUPPLIER_SKILL_MAIN
ON APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL_MAIN.N_SUPPLIER_ID
WHERE
(APPLI_SUPPLIER_SKILL_MAIN.N_SKILL_ID IN (2, 3, 4, 5, 6))
|||If you use sql server 2000,
Code Snippet
CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.RESULT VARCHAR(8000);
SET @.RESULT = '';
SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE
FROM
(
SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE
FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL
ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)
AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)
AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID
)
) AS DATA;
RETURN @.RESULT;
END
GO
SELECT
APPLI_SUPPLIER.N_SUPPLIER_ID,
APPLI_SUPPLIER.V_SUPPLIER_LABEL,
DBO.GET_PROCESS_ITEMS(APPLI_CONSTRUCTION.N_SUPPLIER_ID) V_PROCESS_ITEMS
FROM
APPLI_SUPPLIER
INNER JOIN APPLI_SUPPLIER_SKILL
ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE
(APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
|||Hello Manivannan.D.Sekaran
thank you for your answer.
I use SQL SERVER 2000 SP4
The objective of the function it's to use any query for return a list of element
It's not possible to do a generic function?
|||In SQL Server 2000, we can't able to achieve this. You have to create a separate function. Since you have to embedded this function in your query you have to use the UDF (for each requirement).
In SQL Server 2005, you can achieve this using .NET CLR integration..
|||
thank you for answer .............
I have not chance|||
Hello,
I would like send a list of ID in the function.
How I can do that?
Do you have a idea?
thank you
Code Snippet
CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.RESULT VARCHAR(8000);
SET @.RESULT = '';
SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE
FROM
(
SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE
FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL
ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)
AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN @.SKILL_ID
AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID
)
) AS DATA;
RETURN @.RESULT;
END
GO
|||You can use the SPLIT UDF,
Code Snippet
CREATE FUNCTION SPLITINTOROWS
(
@.LIST AS VARCHAR(8000),
@.DELIMITER AS VARCHAR(10)
)
RETURNS @.LISTOFIDS TABLE (ITEM VARCHAR(8000))
AS
BEGIN
WHILE CHARINDEX(@.DELIMITER, @.LIST) <> 0
BEGIN
INSERT INTO @.LISTOFIDS
VALUES(SUBSTRING(@.LIST,1,CHARINDEX(@.DELIMITER,@.LIST)-1))
SET @.LIST = SUBSTRING(@.LIST, CHARINDEX(@.DELIMITER,@.LIST)+1, LEN(@.LIST))
END
INSERT INTO @.LISTOFIDS VALUES(@.LIST)
RETURN;
END
Code Snippet
CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.RESULT VARCHAR(8000);
SET @.RESULT = '';
SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE
FROM
(
SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE
FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL
ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)
AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (select item from SplitIntoRows(@.SKILL_ID, ','))
AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID
)
) AS DATA;
RETURN @.RESULT;
END
|||
Thank you very much
You are my god
No comments:
Post a Comment