Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

comma-delimited foreign key help!

Posted on 2004-08-24
15
Medium Priority
?
336 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:dwaldner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 8

Expert Comment

by:bukko
ID: 11882360
You could write a function to do it.
0
 
LVL 8

Expert Comment

by:bukko
ID: 11882403
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.

0
 
LVL 3

Author Comment

by:dwaldner
ID: 11882486
@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.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 8

Expert Comment

by:bukko
ID: 11882773
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
0
 
LVL 8

Expert Comment

by:bukko
ID: 11882863
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
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11883231
I'm getting the following error:

The stored procedure completed successfully, but did not return any records.

Any hints?
0
 
LVL 8

Expert Comment

by:bukko
ID: 11883260
Did you pass a valid id list?
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11883270
Yup...double checked and still no results...
0
 
LVL 8

Expert Comment

by:bukko
ID: 11883298
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
0
 
LVL 8

Expert Comment

by:bukko
ID: 11883335
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
0
 
LVL 8

Accepted Solution

by:
bukko earned 2000 total points
ID: 11883498
You can test the logic by running just this bit in query analyser: (with a valid id list of course)

    DECLARE @query varchar(256)
    SET @query = 'SELECT [member Group List].Name FROM [Member Group list] WHERE id IN (1,2,3,4)'
    EXEC( @query )

That should return the actual records.
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11883631
Yes, its returning the actual records, so now I'll try adjusting the code as per your suggestion....
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11883775
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?
0
 
LVL 8

Expert Comment

by:bukko
ID: 11885118
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
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11885996
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
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question