For next loop in sp - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

For next loop in sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-09-21 : 06:58:31

is there anyway I could have a for...next loop in a stored procedure. Something like this which is from my asp.net. I have 28 controls and only the number changes.

lblname ="@strBKP"
For x = 1 To 28
lblname =lblname & x & integer

lblname ="@strBKP"
Next

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 07:07:34

You can use While Loop

Can you explain what you are trying to do in stored procedure?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-09-22 : 04:02:46

Here is my sp at the moment. I just thought it would tidy it up if I could use a loop to define the parameters and set the values instead of this long sp..

CREATE PROCEDURE spRB_UpdateBookingDates
@strBookingDateRef nvarchar(25),

@strBDP1 integer,
@strBDP2 integer,
@strBDP3 integer,
@strBDP4 integer,
@strBDP5 integer,
@strBDP6 integer,
@strBDP7 integer,
@strBDP8 integer,
@strBDP9 integer,
@strBDP10 integer,
@strBDP11 integer,
@strBDP12 integer,
@strBDP13 integer,
@strBDP14 integer,
@strBDP15 integer,
@strBDP16 integer,
@strBDP17 integer,
@strBDP18 integer,
@strBDP19 integer,
@strBDP20 integer,
@strBDP21 integer,
@strBDP22 integer,
@strBDP23 integer,
@strBDP24 integer,
@strBDP25 integer,
@strBDP26 integer,
@strBDP27 integer,
@strBDP28 integer

AS
BEGIN
UPDATE tblRB_BookingDates SET
BD_P1=@strBDP1,
BD_P2=@strBDP2,
BD_P3=@strBDP3,
BD_P4=@strBDP4,
BD_P5=@strBDP5,
BD_P6=@strBDP6,
BD_P7=@strBDP7,
BD_P8=@strBDP8,
BD_P9=@strBDP9,
BD_P10=@strBDP10,
BD_P11=@strBDP11,
BD_P12=@strBDP12,
BD_P13=@strBDP13,
BD_P14=@strBDP14,
BD_P15=@strBDP15,
BD_P16=@strBDP16,
BD_P17=@strBDP17,
BD_P18=@strBDP18,
BD_P19=@strBDP19,
BD_P20=@strBDP20,
BD_P21=@strBDP21,
BD_P22=@strBDP22,
BD_P23=@strBDP23,
BD_P24=@strBDP24,
BD_P25=@strBDP25,
BD_P26=@strBDP26,
BD_P27=@strBDP27,
BD_P28=@strBDP28

WHERE BD_BookingDateRef=@strBookingDateRef

END
GO

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-22 : 04:06:44

I reckon the way you currently have it is the best way

It will currently run in one update statement.

If you use the while loop - it will either run in 28 update statements (this will obviously take about 28 times longer) or you will be sitting with some ugly Dynamic sql.

I say - keep it as it is .

Duane.

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 04:55:02

What you've got there is "normal"

Except you'd be shot! if you were here and used a naming convention like:

@strBDP1 integer

If you have loads of these to write you could make a query that generates the code for you - something along the lines of:


SET NOCOUNT ON
DECLARE	@strTableName	varchar(255)
SET	@strTableName = 'MyTableName'
--
DECLARE @Output TABLE
(
	T_ID	int identity(1,1),
	T_Line	varchar(1000),
	PRIMARY KEY
	(
		T_ID
	)
)
--
INSERT INTO @Output(T_Line) SELECT 'CREATE PROCEDURE dbo.spRB_' + @strTableName
INSERT INTO @Output(T_Line) 
	SELECT	CHAR(9) + CASE WHEN ORDINAL_POSITION = 1 THEN '  ' ELSE ', ' END
			+ '@' + COLUMN_NAME
			+ CHAR(9) + DATA_TYPE
			+ CASE WHEN DATA_TYPE IN
				('char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
				THEN '(' 
					+ CONVERT(varchar(20), CHARACTER_MAXIMUM_LENGTH)
					+ ')'
				WHEN DATA_TYPE IN ('decimal')
				THEN '(' 
					+ CONVERT(varchar(20), NUMERIC_PRECISION)
					+ ','
					+ CONVERT(varchar(20), NUMERIC_SCALE)
					+ ')'
				ELSE ''
				END
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @strTableName
	ORDER BY ORDINAL_POSITION
INSERT INTO @Output(T_Line) SELECT 'AS'
INSERT INTO @Output(T_Line) SELECT 'BEGIN'
INSERT INTO @Output(T_Line) SELECT 'UPDATE' + CHAR(9) + 'dbo.[' + @strTableName + ']'
INSERT INTO @Output(T_Line) SELECT 'SET'
INSERT INTO @Output(T_Line) 
	SELECT	CHAR(9) + CASE WHEN ORDINAL_POSITION = 1 THEN '  ' ELSE ', ' END
			+ '[' + COLUMN_NAME + ']' + CHAR(9) + '= '
			+ '@' + COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @strTableName
	ORDER BY ORDINAL_POSITION
INSERT INTO @Output(T_Line) SELECT 'WHERE' + CHAR(9) + '[MyPrimaryKey] = @MyPrimaryKey'
INSERT INTO @Output(T_Line) SELECT 'END'
INSERT INTO @Output(T_Line) SELECT 'GO'

SELECT	[--SQL] = T_Line
FROM	@Output
ORDER BY T_ID
SET NOCOUNT OFF

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-09-22 : 05:00:21

Thanks to you both - I get your point Kristen ! Knuckles duly rapped !

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 05:08:17

"Knuckles duly rapped"

Don't mind us ...

Try running that SQL Snippet on one of your tables, using Query Analyser or somesuch, it might save you some time!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources