Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: converting SELECT output to string

    I'm looking for some good hints and tips for reprogrammin an old VB module I just found.

    Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.

    E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".

    So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @input... the trick is, I don't know how to do arrays in TSQL as in VB.

    In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.

    Any good examples on how to do this in a sql-server stored proc?

    Thanks,

    Trin

    P.S. This is what I have pieced together this far:

    Code:
    CREATE PROCEDURE findnames
    
    @number int
    
    AS
    
    DECLARE @instrument varchar(50)
    DECLARE @tempinstt varchar(10)
    
    DECLARE medlemcursor CURSOR
    FOR
    	SELECT  [MCPS Kode] 
    	FROM DW.dbo.names(NOLOCK) 
    	WHERE number = @number
    
    OPEN medlemcursor
    
    FETCH NEXT FROM medlemcursor INTO @tempinstt
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    	SET @instrument = @instrument + @tempinstt + '-'
    	FETCH NEXT FROM medlemcursor INTO @tempinstt
    END
    
    CLOSE medlemcursor
    DEALLOCATE medlemcursor
    
    SELECT @instrument
    GO
    Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,
    Last edited by Trinsan; 11-23-05 at 09:12.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    ---u have to intialize ur variable 'instrument' before appending other values.
    Code:
    CREATE PROCEDURE findnames
     
    @number int
     
    AS
     
    DECLARE @instrument varchar(50)
    DECLARE @tempinstt varchar(10)
    set instrument =''
    DECLARE medlemcursor CURSOR
    FOR
    	SELECT [MCPS Kode] 
    	FROM DW.dbo.names(NOLOCK) 
    	WHERE number = @number
     
    OPEN medlemcursor
     
    FETCH NEXT FROM medlemcursor INTO @tempinstt
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    	SET @instrument = @instrument + @tempinstt + '-'
    	FETCH NEXT FROM medlemcursor INTO @tempinstt
    END
     
    CLOSE medlemcursor
    DEALLOCATE medlemcursor
     
    SELECT @instrument
    GO
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Yeap, got it.... and I just added a small substring addendum to cut off the trailing dash.

    Mind boggling NULLs are..

    Is it possible to call such a procedure within a select statement?

    E.g. SELECT number, (EXEC findsnames number), city FROM names
    Last edited by Trinsan; 11-23-05 at 09:38.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just dump your cursor:
    Code:
    CREATE PROCEDURE findnames(@number int)
    AS
    
    declare	@instrument varchar(500) --varchar(50) seemed awfully short...
    
    SELECT	coalesce(@instrument + '-', '') + [MCPS Kode] 
    FROM	DW.dbo.names(NOLOCK) 
    WHERE	number = @number
    
    SELECT	@instrument
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    u cannot call procedure from select statement,
    instead of procedure , create a function,call that function from select statment.

    blindman,
    Poster wants to append record into a string,and ur query will not do that job.
    select @instrument returns NULL.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Corrected code:
    Code:
    CREATE PROCEDURE findnames(@number int)
    AS
    
    declare	@instrument varchar(500) --varchar(50) seemed awfully short...
    
    SELECT	@instrument = coalesce(@instrument + '-', '') + [MCPS Kode] 
    FROM	DW.dbo.names(NOLOCK) 
    WHERE	number = @number
    
    SELECT	@instrument
    GO
    Copy/paste this to try it out:
    Code:
    create table #Names([MCPS Kode] varchar(50), number int)
    insert into #Names([MCPS Kode], number)
    select	'Joe', 1
    UNION
    select	'James', 1
    UNION
    select	'Frank', 2
    UNION
    select	'Janis', 1
    UNION
    select	'Freda', 2
    UNION
    select	'Jeff', 1
    UNION
    select	'Fred', 2
    UNION
    select	'Foster', 2
    UNION
    select	'Jodi', 1
    
    --------------------------------------------------------------------
    declare	@instrument varchar(500) --varchar(50) seemed awfully short...
    declare	@number int
    set	@number = 1
    
    SELECT	@instrument = coalesce(@instrument + '-', '') + [MCPS Kode] 
    FROM	#names(NOLOCK) 
    WHERE	number = @number
    
    SELECT	@instrument
    --------------------------------------------------------------------
    
    drop table #Names
    Output:
    Code:
    James-Janis-Jeff-Jodi-Joe
    Yes, it can be converted into a function if the user wishes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •