LOOPS in stored procedure

Question

Post reply

LOOPS in stored procedure

sandeep shakya

Newbie

Points: 2
More actions
January 18, 2007 at 1:03 am

#176480

I have to use a single stored procedure to insert in two different tables.
I have to store many rows in a table using stored procedure.
How do i use loops inside a stored procedure ??

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ravikumar Pasumarthi SSC Rookie Points: 34 More actions · Answer 1

Hi,

Could you be more clear about how you get the data to the stored procedure?

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

In general, you don't want to. TSQL is set based and works best when you keep that in mind.

For example, where I work, we will usually write a pair of stored procedures for inserts. The first will be very simple and have a list of parameters corresponding to the columns in a table. We'll call this when we have to do a single row insert. When we have to do multi-row inserts, we have a second proc that consists of a single XML parameter. Then we INSERT... SELECT... FROM OPENXML... in order to work with sets of data.

However, post more details about the problem and it'll be easier to come up with an answer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ben Mildren-398690 SSC Journeyman Points: 83 More actions · Answer 3

Simple answer to the question would be:

Declare @x INT, @y INT

SELECT @x = 1

SELECT @y = 2 --(Could have a select statement to get a max value here)

WHILE @x <= @y

BEGIN

--Insert SQL Here

END

But I agree with the above poster try and keep set based if you can...

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

Nice choice. Beats a cursor in most situations.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sandeep shakya Newbie Points: 2 More actions · Answer 5

THANK you all

I really liked it..

here is what i did..

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE PROCEDURE InsertUser

@User_Id varchar(15),

@UserName varchar(50),

@UserCatID tinyint,

@AllowBackEntry bit,

@Description varchar(50) =null,

@TransLimit money =null,

@teller bit ,

@journal bit ,

@vault bit ,

@Entry bit ,

@Verify bit ,

@Post bit ,

@Viewbalance bit

AS

INSERT INTO USERINFO

(User_Id

, UserName

, UserCatID

, AllowBackEntry

, [Description]

, TransLimit

, teller

, journal

, vault

, Entry

, Verify

, Post

, Viewbalance)

VALUES

(@User_Id

, @UserName

, @UserCatID

, @AllowBackEntry

, @Description

, @TransLimit

, @teller

, @journal

, @vault

, @Entry

, @verify

, @Post

, @Viewbalance)

INSERT INTO UserMenuStatus

(

MenuId

, UserId

, Status)

select MenuId,@User_Id,status from catmenu where catid=@Usercatid

GO

>>>>>>>>>>>>>>>>>>>>>>>>>>

I inserted in the "usermenustatus" table from another table "catmenu"..

It worked..