469,626 Members | 901 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

stored procedure make string from table field

Hallo !

I have a Table with a column "ordernumber"

ordernumber
A12
A45
A77
A88

Is it possible to create a stored procedure which makes a string of these column ?

Result: string = ('A12','A45','A77','A88')

Thanks !

aaapaul
Jul 20 '05 #1
3 9168
ng
Hello Paul,
try this Code. It defines a Table in the current database and
creates as stored proc that does the work.

The stored proc uses a cursor to loop thru all entries in
the table.

--Code start
IF EXISTS( SELECT *
FROM sysobjects
WHERE name = 'tblOrderNumbers'
AND type = 'U'
) BEGIN
DROP TABLE tblOrderNumbers

END

CREATE TABLE tblOrderNumbers (OrderNumber varchar(5))

INSERT INTO tblOrderNumbers VALUES ('A12')
INSERT INTO tblOrderNumbers VALUES ('A45')
INSERT INTO tblOrderNumbers VALUES ('A77')
INSERT INTO tblOrderNumbers VALUES ('A88')
GO


IF EXISTS( SELECT *
FROM sysobjects
WHERE name = 'spOrderNumbersString'
) BEGIN
DROP PROCEDURE spOrderNumbersString
END
GO

/*
================================================== ============================
Syno:
Author: www.zankl-it.de, 2003
================================================== ============================
*/
CREATE PROCEDURE spOrderNumbersString
AS
SET NOCOUNT ON

DECLARE @OrderNumber varchar(5),
@Result varchar(1024)
SELECT @Result = '('
DECLARE cOrderNumbers CURSOR FAST_FORWARD READ_ONLY FOR
SELECT * FROM tblOrderNumbers

OPEN cOrderNumbers
FETCH NEXT FROM cOrderNumbers INTO @OrderNumber
WHILE (@@FETCH_STATUS = 0) BEGIN

SET @Result = @Result + '''' + @OrderNumber + ''','

FETCH NEXT FROM cOrderNumbers INTO @OrderNumber

END

CLOSE cOrderNumbers
DEALLOCATE cOrderNumbers

IF LEN(@Result) > 1 BEGIN
SET @Result = LEFT(@Result, LEN(@Result)-1) + ')'
END ELSE BEGIN
SET @Result = NULL
END
SELECT @Result

RETURN 0
GO

EXEC spOrderNumbersString
--Code End

Zankl-IT, Berlin
www.zankl-it.de

pa**************@vit.linhardt.com (aaapaul) wrote in message news:<9d**************************@posting.google. com>...
Hallo !

I have a Table with a column "ordernumber"

ordernumber
A12
A45
A77
A88

Is it possible to create a stored procedure which makes a string of these column ?

Result: string = ('A12','A45','A77','A88')

Thanks !

aaapaul

Jul 20 '05 #2
I think maybe this should work (Tablename = tOrders):

CREATE PROCEDURE spGetOrderNumbers AS
DECLARE @OrderIDs VARCHAR(250)
BEGIN
SELECT @OrderIDs = COALESCE(@OrderIDs + ', ', '') + '''' + OrderNumber + ''''
FROM tOrders
SET @OrderIDs = '(' + @OrderIDs + ')'
SELECT @OrderIds
END
GO

Martin
Jul 20 '05 #3
>> Is it possible to create a stored procedure which makes a string of
this column ? <<

Yes, but it will be a proprietary kludge, using either proceudral code
or unpredictable behavior. The whole idea of a Client/Server system
is that the back end does the data retrieval and the front end does
the display and formatting.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tavish Muldoon | last post: by
5 posts views Thread by Rob Wire | last post: by
5 posts views Thread by microsoft.private.windows.netserver.setup | last post: by
reply views Thread by SOI_0152 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.