UDF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UDF

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-11-03 : 06:26:48

Dear guys,

Is the UDF supports the INTO clause.I want to create a function and i used a query with INTO clause.The function is not executing.

Is there any solution,the query is given below, i want this query as a function.

if object_id('temp1')is not null
drop table temp1

SELECT
'sopnumbe' = T3.PT_UD_Key,
'LNITMSEQ' = T3.LNITMSEQ,
'Note1Date' = T3.DATE1,
'Note1' = cast (T4.TXTFIELD as varchar(100))
into temp1
FROM ext00150 T4 RIGHT JOIN
(
SELECT
DISTINCT T1.PT_UD_Key,
T2.LNITMSEQ,
T2.DATE1,
T2.TIME1
FROM
ext00150 AS T1
CROSS APPLY
two.dbo.fn_GetTopOrders( T1.PT_UD_Key, 3) AS T2
) AS T3
ON T4.PT_UD_Key=T3.PT_UD_Key
AND T4.LNITMSEQ=T3.LNITMSEQ
ORDER BY T3.PT_UD_Key ,
T3.LNITMSEQ DESC

--TO Categorise the notes as per date
SELECT
MAX(CASE WHEN Seq=1 THEN sopnumbe ELSE null END) sopnumbe,
MAX(CASE WHEN Seq=1 THEN Note1Date ELSE NULL END) AS Note1Date,
MAX(CASE WHEN Seq=1 THEN Note1 ELSE NULL END) AS Note1,
MAX(CASE WHEN Seq=2 THEN Note1Date ELSE NULL END) AS Note2Date,
MAX(CASE WHEN Seq=2 THEN Note1 ELSE NULL END) AS Note2,
MAX(CASE WHEN Seq=3 THEN Note1Date ELSE NULL END) AS Note3Date,
MAX(CASE WHEN Seq=3 THEN Note1 ELSE NULL END) AS Note3
from
(
SELECT ROW_NUMBER() OVER(PARTITION BY sopnumbe ORDER BY lnitmseq DESC) AS Seq,*
FROM temp1
)t
GROUP BY sopnumbe

Guys,
your reponse will be appreciated.

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 06:45:18

why each time drop and create?cant you just create table temp1 once outside and just use INSERT...SELECT in udf?

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-11-03 : 06:47:36

The whole record will be updated dynamically and periodically, so only i use to create the temp table and dropping it.

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 07:06:05

but you're not creating a temp table. You're creating permanent table itslef (only name is temp). temporary tables will have # sign preceding the name so it should be #temp1. ALso if this udf is executed concurrently by two users, usage of permanent table will cause error.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-03 : 07:08:18

try something like this


if object_id('temp1')is not null
drop table temp1

;WITH temp1(sopnumbe, LNITMSEQ, Note1Date, Note1)
AS 
(
		SELECT
			'sopnumbe' = T3.PT_UD_Key,
			'LNITMSEQ' = T3.LNITMSEQ,
			'Note1Date' = T3.DATE1,
			'Note1' = CAST (T4.TXTFIELD AS VARCHAR(100))
--		INTO temp1
		FROM ext00150 T4 RIGHT JOIN
		(
			SELECT DISTINCT 
					T1.PT_UD_Key,
					T2.LNITMSEQ,
					T2.DATE1,
					T2.TIME1
			FROM ext00150 AS T1
			CROSS APPLY two.dbo.fn_GetTopOrders( T1.PT_UD_Key, 3) AS T2
		) AS T3 ON T4.PT_UD_Key=T3.PT_UD_Key
			AND T4.LNITMSEQ=T3.LNITMSEQ
--		ORDER BY T3.PT_UD_Key, T3.LNITMSEQ DESC
)

--TO Categorise the notes as per date
SELECT
	MAX(CASE WHEN Seq=1 THEN sopnumbe ELSE null END) sopnumbe,
	MAX(CASE WHEN Seq=1 THEN Note1Date ELSE NULL END) AS Note1Date,
	MAX(CASE WHEN Seq=1 THEN Note1 ELSE NULL END) AS Note1,
	MAX(CASE WHEN Seq=2 THEN Note1Date ELSE NULL END) AS Note2Date,
	MAX(CASE WHEN Seq=2 THEN Note1 ELSE NULL END) AS Note2,
	MAX(CASE WHEN Seq=3 THEN Note1Date ELSE NULL END) AS Note3Date,
	MAX(CASE WHEN Seq=3 THEN Note1 ELSE NULL END) AS Note3
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY sopnumbe ORDER BY lnitmseq DESC) AS Seq,*
	FROM temp1
)t
GROUP BY sopnumbe

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-11-03 : 08:22:19

Hi guys,

I have tried the same as below mentioned function,
really thanks for u'r replies.

if object_id('fn_fetch_sop_notes')is not null
drop function fn_fetch_sop_notes
go
create function fn_fetch_sop_notes()
returns @p_zsl_notes table
(
sopnumbe varchar(30) primary key,
note1date datetime,
note1 text,
note2date datetime,
note2 text,
note3date datetime,
note3 text
)
as
/*
Execution:-

select * from .dbo.fn_fetch_sop_notes()

*/
begin
declare @l_temp table
(
sopnumbe varchar(300),
LNITMSEQ int ,
Note1Date datetime ,
Note1 varchar(150)
)

insert into @l_temp
SELECT
T3.PT_UD_Key ,
T3.LNITMSEQ ,
T3.DATE1 ,
CAST (T4.TXTFIELD AS VARCHAR(100))
FROM ext00150 T4 RIGHT JOIN
(
SELECT
DISTINCT T1.PT_UD_Key,
T2.LNITMSEQ,
T2.DATE1,
T2.TIME1
FROM
ext00150 AS T1
CROSS APPLY
two.dbo.fn_GetTopOrders( T1.PT_UD_Key, 3) AS T2
) AS T3
ON T4.PT_UD_Key=T3.PT_UD_Key
AND T4.LNITMSEQ=T3.LNITMSEQ
ORDER BY T3.PT_UD_Key ,
T3.LNITMSEQ DESC

--TO Categorise the notes as per date

insert into @p_zsl_notes
SELECT
MAX(CASE WHEN Seq=1 THEN sopnumbe ELSE null END) sopnumbe,
MAX(CASE WHEN Seq=1 THEN Note1Date ELSE NULL END) AS Note1Date,
MAX(CASE WHEN Seq=1 THEN Note1 ELSE NULL END) AS Note1,
MAX(CASE WHEN Seq=2 THEN Note1Date ELSE NULL END) AS Note2Date,
MAX(CASE WHEN Seq=2 THEN Note1 ELSE NULL END) AS Note2,
MAX(CASE WHEN Seq=3 THEN Note1Date ELSE NULL END) AS Note3Date,
MAX(CASE WHEN Seq=3 THEN Note1 ELSE NULL END) AS Note3
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY sopnumbe ORDER BY lnitmseq DESC) AS Seq,*
FROM @l_temp
)t
GROUP BY sopnumbe

return
end

cool...,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources