funny sql

The following sql updates 300 records(3000 records in the
marc_POt_lu_rd_post_code table) but the select element only returns one row.
I am attempting to update the 3000 rows which it does but it does it
incorrectly in that the results set from the select portion does not match
what the results set returns after the update. I added the extra postcode
criteria in the select to isolate what the update does but it still updates
the 3000 rows. Weird?
UPDATE marc_POt_lu_rd_post_code
SET County_id = c.County_id,
County_desc = c.County_Desc,
Parent_County_Id = c.Parent_County_Id,
Parent_County_desc = c.County_desc,
Sector_Id = d.Sector_Id,
Sector_Desc = d.Sector_Desc,
Area_Id = e.Area_Id,
Area_Desc = e.Area_Desc
-- Select *
FROM Pot_lu_County_Area_PostCodes a,
QUINN_st..GET_BCP_H_POSTCODES b,
Pot_lu_county c,
Pot_lu_Sectors d,
Pot_lu_Areas e
WHERE a.Postcode = b.Four_Char_Post_Codes
AND b.COUNTY = c.County_Desc
AND b.SECTOR = d.Sector_Desc
AND b.AREA = e.Area_Desc
and a.Postcode = b.Four_Char_Post_Codes
and b.Four_Char_Post_Codes = 'mk40'found the issue
"marcmc" wrote:

> The following sql updates 300 records(3000 records in the
> marc_POt_lu_rd_post_code table) but the select element only returns one ro
w.
> I am attempting to update the 3000 rows which it does but it does it
> incorrectly in that the results set from the select portion does not match
> what the results set returns after the update. I added the extra postcode
> criteria in the select to isolate what the update does but it still update
s
> the 3000 rows. Weird?
> UPDATE marc_POt_lu_rd_post_code
> SET County_id = c.County_id,
> County_desc = c.County_Desc,
> Parent_County_Id = c.Parent_County_Id,
> Parent_County_desc = c.County_desc,
> Sector_Id = d.Sector_Id,
> Sector_Desc = d.Sector_Desc,
> Area_Id = e.Area_Id,
> Area_Desc = e.Area_Desc
> -- Select *
> FROM Pot_lu_County_Area_PostCodes a,
> QUINN_st..GET_BCP_H_POSTCODES b,
> Pot_lu_county c,
> Pot_lu_Sectors d,
> Pot_lu_Areas e
> WHERE a.Postcode = b.Four_Char_Post_Codes
> AND b.COUNTY = c.County_Desc
> AND b.SECTOR = d.Sector_Desc
> AND b.AREA = e.Area_Desc
> and a.Postcode = b.Four_Char_Post_Codes
> and b.Four_Char_Post_Codes = 'mk40'
>

Function for return element list from a query

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