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
