Newbie Stored Procedure Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Newbie Stored Procedure Question

Author

Topic

kev510
Starting Member

5 Posts

Posted - 2006-09-25 : 15:11:07

Hi everybody. I am not sure if I'm even posting in the right forum... I tried my best.

I need to write a stored procedure that'll work with two values, @FromValue and @ToValue.

In Query Analyzer, when this stored procedure is executed with user-given values (@FromValue/@ToValue), I need it to loop until the program reaches the ToValue number.

Lets say I executed the SP with given values @FromValue = 1 and @ToValue = 5, the result should be

1, 2, 3, 4, 5

Listing out all numbers 1 to 5, with a comma and space after each number.

Thank you for taking your time to help me.

-Kevin

Kristen
Test

22859 Posts

Posted - 2006-09-25 : 16:04:20

Something like this?


CREATE PROCEDURE dbo.MyProcedure
	@FromValue int,
	@ToValue int,
	@strOutput varchar(8000) = NULL OUTPUT
AS
WHILE @FromValue <= @ToValue
BEGIN
	SELECT @strOutput = COALESCE(@strOutput + ', ', '') + CONVERT(varchar(20), @FromValue),
	@FromValue = @FromValue + 1
END
GO

DECLARE @strOutput varchar(8000)
EXEC dbo.MyProcedure 1, 5, @strOutput OUTPUT
SELECT @strOutput

Have to say I wouldn't do it with a LOOP, but ...

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-25 : 16:07:53

Set based, with no loop


declare @x varchar(40)
declare @FromValue int
declare @ToValue int

select @FromValue = 6, @ToValue = 14

select @x = isnull(@x+', ','')+ convert(varchar(20),number)
from	(
	select top 100 percent
		number 
	from
		-- Function from script library
		F_TABLE_NUMBER_RANGE(@FromValue,@ToValue)
	order by
		number
	) a

select x= @x

Results:


x                                        
---------------------------------------- 
6, 7, 8, 9, 10, 11, 12, 13, 14

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources