dwaldner
asked on
comma-delimited foreign key help!
Hi folks:
I have a Profiles table that, among other things, contains all the groups a member can be a part of. I have a field called [Member Groups] that contains a comma-seperated value of all the foreign keys from the table [Member Group List]. So, in the field, I have this:
Member Groups
------------------
4, 5, 6, 7
But what I really want is something like this to return:
Member Groups
------------------
Baseball Club, Basketball Club, Hockey Club, Football Club
I know already that this is not good database practice, but I'm working on existing design restrictions. Does anybody know of a stored procedure, or a query, or some way of getting from where I am to where I want to be? Thanks in advance,
Dan
I have a Profiles table that, among other things, contains all the groups a member can be a part of. I have a field called [Member Groups] that contains a comma-seperated value of all the foreign keys from the table [Member Group List]. So, in the field, I have this:
Member Groups
------------------
4, 5, 6, 7
But what I really want is something like this to return:
Member Groups
------------------
Baseball Club, Basketball Club, Hockey Club, Football Club
I know already that this is not good database practice, but I'm working on existing design restrictions. Does anybody know of a stored procedure, or a query, or some way of getting from where I am to where I want to be? Thanks in advance,
Dan
You could write a function to do it.
If dynamic SQL is ok, the query could be something like:
DECLARE @query varchar(32)
SET @query = 'SELECT * FROM memberGroups WHERE id IN (' + @member_groups + ')'
EXEC( @query )
Then build the return string from the results.
DECLARE @query varchar(32)
SET @query = 'SELECT * FROM memberGroups WHERE id IN (' + @member_groups + ')'
EXEC( @query )
Then build the return string from the results.
ASKER
@bukko,
That last bit I think is exactly what I need, however:
I'm terrible with writing stored procedures...how do you modify your code to do what I need.
That last bit I think is exactly what I need, however:
I'm terrible with writing stored procedures...how do you modify your code to do what I need.
I have assumed the column Name in [Member Group List] table.
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @member_groups + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
GO
Call this stored proc as follows:
DECLARE @results varchar(1024)
EXEC dbo.ids_to_names @id_list = '1,2,3,4,5', @results OUTPUT
SELECT @results
You may need to change some of the object names, etc.
Plus there is probably a better way to do this.
Regards
bukko
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @member_groups + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
GO
Call this stored proc as follows:
DECLARE @results varchar(1024)
EXEC dbo.ids_to_names @id_list = '1,2,3,4,5', @results OUTPUT
SELECT @results
You may need to change some of the object names, etc.
Plus there is probably a better way to do this.
Regards
bukko
Whoops - forgot to drop the temp table.
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @member_groups + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
DROP TABLE #temp
GO
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @member_groups + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
DROP TABLE #temp
GO
ASKER
I'm getting the following error:
The stored procedure completed successfully, but did not return any records.
Any hints?
The stored procedure completed successfully, but did not return any records.
Any hints?
Did you pass a valid id list?
ASKER
Yup...double checked and still no results...
Don't forget, call this stored proc as follows:
DECLARE @results varchar(1024)
EXEC dbo.ids_to_names @id_list = '1,2,3,4,5', @results OUTPUT
SELECT @results
DECLARE @results varchar(1024)
EXEC dbo.ids_to_names @id_list = '1,2,3,4,5', @results OUTPUT
SELECT @results
Ok, so I left an incorrect variable name in there.
Try this version:
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @id_list + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
DROP TABLE #temp
GO
Try this version:
CREATE PROCEDURE dbo.ids_to_names (
@id_list varchar(32),
@results varchar(1024) OUTPUT
)
AS
DECLARE @query varchar(256), @name VARCHAR(32)
SET @query = 'SELECT [member Group List].Name INTO #temp FROM [Member Group list] WHERE id IN (' + @id_list + ')'
EXEC( @query )
DECLARE temp_cursor CURSOR FOR
SELECT #temp.name FROM #temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @name
ELSE
SET @results = @result + ',' + @name
FETCH NEXT FROM temp_cursor INTO @name
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
DROP TABLE #temp
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, its returning the actual records, so now I'll try adjusting the code as per your suggestion....
ASKER
Almost there, bukko....
Message I get when I execute the SP as per your instructions:
Server: Msg 208, Level 16, State 1, Procedure ids_to_names, Line 1
Invalid object name '#temp'.
It appears to have a problem with the temp table...any thoughts?
Message I get when I execute the SP as per your instructions:
Server: Msg 208, Level 16, State 1, Procedure ids_to_names, Line 1
Invalid object name '#temp'.
It appears to have a problem with the temp table...any thoughts?
I think I know why.
Because the temp table is being created in the dynamic SQL string, it's probably out of scope when referenced by the rest of the proc.
You could name the table ##temp instead, but this assumes that no-one else will be running this proc or you will get concurency issues.
A much better alternative would be to create the table explicitly before the EXEC('SELECT... etc.
The dynamic SQL should still be able to reference the table as it is within scope.
This will be something like:
CREATE TABLE #temp (
id int,
name varchar(32)
)
What does the source table look like exactly?
bukko
Because the temp table is being created in the dynamic SQL string, it's probably out of scope when referenced by the rest of the proc.
You could name the table ##temp instead, but this assumes that no-one else will be running this proc or you will get concurency issues.
A much better alternative would be to create the table explicitly before the EXEC('SELECT... etc.
The dynamic SQL should still be able to reference the table as it is within scope.
This will be something like:
CREATE TABLE #temp (
id int,
name varchar(32)
)
What does the source table look like exactly?
bukko
ASKER
That's okay bukko, I did as you suggested and it worked just fine...
I don't think we'll be getting concurrency issues; the chances of that happening on our servers are VERY small.
Thanks for all your help
Dan
I don't think we'll be getting concurrency issues; the chances of that happening on our servers are VERY small.
Thanks for all your help
Dan