Making Concat Function Generic ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Making Concat Function Generic ?

Author

Topic

anandc
Starting Member

20 Posts

Posted - 2005-09-02 : 06:46:19

I have got the below code from this forum, I want to make this generic. I mean I want this function to take below 3 parameters and then return the concatenated string as output.
1) Table name
2) Output Column Name
3) Array of Name-Value pairs to be used in the where clause.

Is it possible and How ?

Thanks

[CODE]
USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Authors
WHERE State = @State
ORDER BY au_lname

RETURN @Output
END
GO
[/CODE]

- Anand

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 07:04:24

[code]
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
...
[/code]
can be done slightly more easily with
[code]
DECLARE @Output VARCHAR(8000)
-- Do NOT pre-set any value

SELECT @Output = COALESCE(@Output + ', ', '')
+ au_lname
[/code]
Having said that I don't think what you want is possible.

You could probably do it fairly easily in a stored procedure that used dynamic SQL - but that would a) be pretty inefficient and b) would require the user had permissions on the table, not just the SProc.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources