Create Function Help - 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)

Create Function Help

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-03-15 : 08:57:42

I have the following function that I was able to put together with the help of the following article [url][/url]http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.

USE database1
GO
CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
GO

prt_Mark | dwg_Seq
12 | 12a,23b
25c | 1b,5e,8d,100as

I get the following errors

Msg 325, Level 15, State 1, Line 2
Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@prt_Mark".
Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'To'.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-15 : 09:56:44

HI,
No Need to write Function
this will be useful

deCLARE @StrConcat tABLE (col1 nvarchar(10),col2 nvarchar(10))

insert into @StrConcat
select 'db1','host1' union all
select 'db1','host2' union all
select 'db1','host3' union all
select 'db2','host1' union all
select 'db2','host2' union all
select 'db3','host2' union all
select 'db3','host3'

Declare @col1 varchar(100)
Set @col1 = 'Db1'

select col1, stuff( ( select ', '+ col2 from @StrConcat t1 where t2.col1 = t1.col1 for xml path('')),1,1,'')
from @StrConcat t2
where col1 = @Col1
group by col1 order by col1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-15 : 11:23:43

quote:
I'm having some problems with it

what is the problem ?

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-15 : 11:54:30

[code]
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
[/code]

based on that the first Case evaluation will always be true. you SET @Concat = '' then evaluate if it = '' it will always be true.

It seems your intent is to concat the values returned in the rows into a string. A Case statement won't do that..it will return rows based on the evaluation.

The variable declaration error is fruit of the poisoned tree by way of the Create Function error returned. What version of SQL are you trying to create this on? and what version is "database1" ?

What is sample source data, and what is desired output result?

Poor planning on your part does not constitute an emergency on my part.

chapo
Starting Member

39 Posts

Posted - 2008-03-15 : 12:28:41

Thanks for the quick responses everyone.

dataguru1971

I'm using SQL 2005
I'm no sure if this is the information you are looking when it comes to my database version 9.00.1399.06

Here is some sample data
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results.
wo_No | prt_Mark | dwg_Seq |
324037 | d400 | 1S2 |
324037 | d400 | A1 |
324037 | d400 | 1 |
219001 | 56 | 2B |
219001 | 56 | 2C |
219001 | 56 | 2C |

What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark.
324037 | d400 | 1S2,A1,1 |
219001 | 56 | 2B,2C |

If there is anything else that you required let me know.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-15 : 22:45:44

Actually, I see where you were going, and my comment was wrong for that particular scenario (as to the case order)

as I said with your GO statements, you can't put those in the "middle" of the function or errors will throw.

you were clearly following the syntax from here:http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
but were slightly off..

your variable needs to be at least 3 to work since the field value being passed can be 3 characters. Also, add a group by to ensure that unique results are returned.

The following works for me to get your desired results:



CREATE TABLE foo (wo_No int not null
		  ,prt_Mark nvarchar(4) not null
		  ,dwg_Seq nvarchar(3) not null)

Insert Into foo (wo_No,prt_mark,dwg_Seq)

Select 324037,  'd400',  '1S2' UNION ALL
Select 324037,  'd400',  'A1' UNION ALL
Select 324037,  'd400',  '1' UNION ALL
Select 219001,  '56',  '2B' UNION ALL
Select 219001, '56', '2C' UNION ALL
Select 219001, '56',  '2C' 
go

CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(4))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
			WHEN '' THEN dwg_Seq
			ELSE @Output + ', ' + dwg_Seq
			END
FROM foo
WHERE prt_Mark = @prt_Mark
Group by dwg_Seq
ORDER BY dwg_Seq
RETURN @Output
END
GO


Select wo_no,prt_Mark, dbo.Concatdwg_Seq (prt_mark)
FROM foo
GROUP by wo_no,prt_Mark, dbo.Concatdwg_Seq (prt_mark)
ORDER By wo_no
GO

Drop Table foo
Drop Function dbo.Concatdwg_Seq

However, I am not sure why you got the errors, but I got this to work.


wo_no	prt_Mark	(No column name)
219001	56	2B, 2C
324037	d400	1, 1S2, A1

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources