asked on

Dynamic Enumerate items procedure

In Microsoft Access I have used a custom function (usually called DList) which would accept the table name, field name, and search criteria as parameters. It would output a comma seperated list of the values in FieldName from the table where the search criteria is true.

In SQL Server 2000, is there a way to have a stored procedure return the same string output given the same input parameters?

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

If you want the procedure to return the value to a variable see attached. And use it like this:

declare @list varchar(2000)
exec Dlist 'class', 'period', @value=@list output
select @list

create procedure Dlist
@table varchar(200),
@column varchar(200),
@value varchar(2000) output
as
	declare @strSQL nvarchar(2000)
	declare @params nvarchar(2000)
	declare @x1 nvarchar(2000)


	set @strSQL = 'select @x = coalesce(@x + '','', '''') + cast(' + @column + ' as varchar) from ' + @table
	set @params = N'@x varchar(2000) OUTPUT'

	exec sp_executeSQL @strSQL, @params, @x = @x1 OUTPUT

	set @value = @x1

Open in new window

KCTechNet

ASKER

had to make a couple changes, but worked good.

CREATE PROCEDURE [dbo].[usp_DList]
(
@table varchar(200),
@column varchar(200),
@where varchar(200)
)
AS
declare @strSQL as nvarchar(2000)
declare @params as nvarchar(2000)
declare @x1 as nvarchar(2000)

set @strSQL = 'select @x = coalesce(@x + '','', '''') + cast(' + @column + ' as varchar) from ' + @table + ' where ' + @where
set @params = N'@x varchar(2000) OUTPUT'

exec sp_executeSQL @strSQL, @params, @x = @x1 OUTPUT

select @x1
GO

KCTechNet

ASKER

I chaned it a bit more to make it extremely flexible:

CREATE PROCEDURE [dbo].[usp_DList]
(
@SQLfrom varchar(2000),
@column varchar(25)
)
AS
declare @strSQL as nvarchar(2000)
declare @params as nvarchar(2000)
declare @x1 as nvarchar(2000)

set @strSQL = 'select @x = coalesce(@x + '','', '''') + cast(' + @column + ' as varchar) from ' + @SQLfrom
set @params = N'@x varchar(2000) OUTPUT'

exec sp_executeSQL @strSQL, @params, @x = @x1 OUTPUT

select @x1

-- sample use: exec dbo.usp_DList 'LanguageCodes as lc Inner Join ServicesLanguages sl on lc.LanguageCodeID = sl.LanguageCodeID Where ServiceID = 13','Language'
GO