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 2005 Forums
 Transact-SQL (2005)
 Loop through table

Author  Topic 

Harry C
Posting Yak Master

148 Posts

Posted - 2008-06-04 : 12:40:48
I have a table called _phy_greenville. Its a table that was imported from an excel file. I need to take the values in this table, and pass them to the following stored procedure. This stored proc create the physician record correctly for me when doing one record at a time. What I need to do is pass ALL record in this table (_phy_greenville) into this stored proc. Is there anyway I can loop through, or do some sort of bulk insert?

I tried the following, but obviously this does not work

BEGIN TRANSACTION

EXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_Greenville

SELECT * FROM Person WHERE PersonOrganizationID = 31

ROLLBACK TRANSACTION


Here is the whole stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pInsertPersonEx]
(
@Active bit,
@PersonPrefix varchar(5),
@PersonFirstName varchar(50),
@PersonLastName varchar(50),
@PersonSuffix varchar(20),
@PersonEmail varchar(100),
@PersonPassword varchar(20),
@PersonOrganizationID int,
@PersonDepartment varchar(50),
@PersonTitle varchar(100),
@PersonPhone varchar(10),
@PersonFax varchar(10),
@PersonInfoRequested bit,
@PersonHCTrained bit = NULL,
@PersonHCTrainedDate DateTime = NULL,
@AddressAddress1 varchar(100),
@AddressAddress2 varchar(100),
@AddressCity varchar(100),
@StateId int,
@AddressPostalCode varchar(10),
-- @DepartmentID int,
@RoleID int
)
as

IF @PersonHCTrainedDate = '01/05/1900' SET @PersonHCTrainedDate = NULL

set nocount on

declare @PersonId int,
@AddressTypeID int

insert Person
(
Active,
PersonPrefix,
PersonFirstName,
PersonLastName,
PersonSuffix,
PersonEmail,
PersonPassword,
PersonOrganizationID,
PersonDepartment,
PersonRegistrationDate,
PersonLicenseAgreement,
PersonTitle,
PersonPhone,
PersonFax,
PersonInfoRequested,
PersonHCTrained,
PersonHCTrainedDate
)
values
(
@Active,
@PersonPrefix,
@PersonFirstName,
@PersonLastName,
@PersonSuffix,
@PersonEmail,
convert(varbinary,@PersonPassword),
@PersonOrganizationID,
@PersonDepartment,
getdate(),
0, /* TODO need to get this from form */
@PersonTitle,
@PersonPhone,
@PersonFax,
@PersonInfoRequested,
@PersonHCTrained,
@PersonHCTrainedDate
)

set @PersonID = IDENT_CURRENT('Person')

/* look up the default address type */
select @AddressTypeID = AddressTypeID
from AddressType
where AddressTypeDisplayName = 'Work'

exec pInsertPersonAddress
@AddressAddress1,
@AddressAddress2,
@AddressCity,
@AddressPostalCode,
@StateID,
@PersonID,
@AddressTypeID,
1 /* this proc always inserts the default address */

/* - Schema change. Department now a varchar
exec pInsertPersonDepartment
@PersonID,
@DepartmentID,
1 /* this proc always inserts the default department */
*/
exec pInsertPersonRole
@PersonID,
@RoleID

INSERT INTO tblHospitalsCoordinated
(
intPersonID
,intHospitalID
,dtmCreatedDate
,dtmModifiedDate
,strModifiedBy
)
VALUES
(
@PersonID
,@PersonOrganizationID
,GetDate()
,GetDate()
,''
)

select @PersonID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 13:19:14
[code]DECLARE @PKCol int

SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville

WHILE @PKCol IS NOT NULL
BEGIN
your query here


SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville
WHERE PKCol > @PKCol

END[/code]

I still cant understand why you are ROLLBACKing transaction in your query though
Go to Top of Page

Harry C
Posting Yak Master

148 Posts

Posted - 2008-06-04 : 13:40:12
I am just Rolling it back because I was testing it. No other reason. Let me try that. But wait, what exactly would I put for the insert


DECLARE @PKCol int

SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville

WHILE @PKCol IS NOT NULL
BEGIN

'How do i pass the right Params to the stored proc?
EXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_Greenville WHERE ID = @PKCol

SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville
WHERE PKCol > @PKCol

END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 13:53:00
You cant do EXEC...SELECT. you need to get values in variables and pass it to sp


DECLARE @PKCol int

SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville

WHILE @PKCol IS NOT NULL
BEGIN


SELECT @FirstName=FirstName,@LastName=LastName,@Suffix=Suffix,
@Email=Email,@FullName=CAST((LEFT(FirstName, 1) + LastName)as varbinary),@PrimarySpecialty=PrimarySpecialty, @OffPhone=REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''),
@Street=Street,@City=City,@Zip=Zip
FROM _Phy_Greenville WHERE ID = @PKCol

'How do i pass the right Params to the stored proc?
EXEC pInsertPersonEX SELECT 1, 'Dr.', @FirstName, @LastName, @Suffix, @Email, @FullName, 31, @PrimarySpecialty, 'Student', @OffPhone, NULL, 0, 0, NULL, @Street, NULL, @City, 41, @Zip,3 FROM _Phy_Greenville WHERE ID = @PKCol


SELECT @PKCol=MIN(PKCol)
FROM _phy_greenville
WHERE PKCol > @PKCol

END


Also remember to declare the variables used before while loop
Go to Top of Page

Harry C
Posting Yak Master

148 Posts

Posted - 2008-06-04 : 15:02:47
Got it, thanks!

HC
Go to Top of Page
   

- Advertisement -