June 13, 2008 at 1:19 pm
Hi,
I got both versions working but each one gives me back multiple rows not just one. Here's what I have:
(can it be made to vary the column and table?)
/*
create function fn_pes_COALESCE1(@keyvalue int)
returns varchar(1000) as
begin
declare @retstr varchar(1000)
set @retstr = ''
select @retstr = @retstr + case when @retstr <> '' then ',' else '' end + coalesce(ptype, '')
from stringtest
where projectid = @keyvalue
return @retstr
end
go
CREATE FUNCTION fn_pes_CONCAT1 (@keyvalue INT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @retstr VARCHAR(1000)
SELECT @retstr = ISNULL(@retstr + ', ', '') + ptype
FROM stringtest
WHERE projectid=@keyvalue
RETURN @retstr
END
GO
*/
SET NOCOUNT ON
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stringtest')
DROP TABLE stringtest
GO
CREATE TABLE stringtest
(
ident tinyint identity,
projectid integer NULL,
delday varchar(20) NULL,
ptype varchar(50) NULL
)
GO
INSERT stringtest VALUES(7,'Monday','Certified')
INSERT stringtest VALUES(8,'Tuesday','Certified')
INSERT stringtest VALUES(8,'Tuesday','ACH')
INSERT stringtest VALUES(10,'Wednesday','Direct')
INSERT stringtest VALUES(7,'Thursday','Direct')
INSERT stringtest VALUES(8,'Wednesday','Direct')
INSERT stringtest VALUES(12,'Friday','Certified')
INSERT stringtest VALUES(13,'Thursday','Certified')
INSERT stringtest VALUES(8,'Thursday','Certified')
GO
SET NOCOUNT OFF
GO
select dbo.fn_pes_COALESCE1(7) from stringtest
select dbo.fn_pes_CONCAT1(8) from stringtest
GO
June 13, 2008 at 3:00 pm
Fixed the problem. I was adding 'from stringtest' to the end of the select. The functions are successful if I only use 'select dbo.fn_pes_CONCAT1(8)' and do not add 'from stringtest'.
I will still look at ways to expand this to look at any column and any table. Our tables all have the same key so I don't have to vary the key just the keyvalue is fine. I'm thinking I will have to create a string and EXEC() the string to add the two extra variables. I don't think T-SQL allows variable table names. I haven't hit on how to do it yet if you think of a way, bless you.
Warm regards,
June 16, 2008 at 4:45 am
I think there's a couple of reasons for the multiple rows. If you're running the function with an actual value
select dbo.fn_pes_CONCAT1(8) from stringtest
then you're concatenating the values for projectid 8, and then displaying them once for each row in stringtest. If you change it to the field name
select dbo.fn_pes_CONCAT1(projectid) from stringtest
then it should work.
The other reason for multiple rows is that your function is only grouping on projectid. Your initial question was for a list for each XX-YY combination (would that be ident-projectid, or delday-projectid?). That's why my initial answer has a function with two variables, but your function only looks at one.
And yes, you can't have a variable table name. You'd need to create an sql string dynamically - but that does have it's own consequences. There's the dreaded sql injection (google it!) but probably more relevant is the performance hit it can have as it has to generate a new execution plan every time, whereas the stored procedure doesn't. It may be that you get better performace with a function for each table you need to use it on, and it will certainly be quicker to set up.
June 16, 2008 at 5:56 am
Please see the following to get the best performance out of a concatenation function...
http://qa.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply