using a stored proc to insert and return a value

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

SQL Server Development (2000)

using a stored proc to insert and return a value

Author

Topic

bede@webfoot.co.nz
Starting Member

2 Posts

Posted - 2000-11-23 : 16:41:09

Hi i have buil the following Proc,
I am using asp pages to create a user
and fill in up to 3 additonal tables in
my relational database, unfortunitly i have to either pass an array to the proc through some means into an area key table(not included here)

so I would like to know,
a)If I can pass an array to the proc and loop through the array inserting each row,
b)if I can use the following and return the @U_ID value so I may achive the above in an asp page

CREATE PROCEDURE [sp_Create_User]

( @U_User [nvarchar](50),
@U_status [nvarchar](50),
@U_Name [nvarchar](50),
@U_LastName [nvarchar](50),
@U_Job_Cat [nvarchar](50),
@U_jobTitle [nvarchar](50),
@U_Phone [nvarchar](50),
@U_Address [nvarchar](50),
@U_City [nvarchar](50),
@U_Zip [nvarchar](50),
@U_State [nvarchar](50),
@U_Country [nvarchar](50),
@Press [nvarchar](3),
@password [nvarchar](50),
@datenow [nvarchar](50),
@mstat [nvarchar](50),

/* Add The Optional Questions */
@num_employ_2 [nvarchar](50),
@Num_fac_3 [nvarchar](50),
@Ind_group_4 [nvarchar](50),

/* Add Subscription Questions */
@U_Company_Products_2 [ntext],
@U_Company_Job_Description_3 [ntext],
@U_Company_Purchase_4 [ntext],
@U_Date_5 [varchar](50))
As

DECLARE @U_ID int

EXEC sp_insert_Resource_User_1
@U_User = @U_User,
@U_status = @U_status,
@U_Name = @U_Name,
@U_LastName = @U_LastName,
@U_Job_Cat = @U_Job_Cat,
@U_jobTitle = @U_jobTitle,
@U_Phone = @U_Phone,
@U_Address = @U_Address,
@U_City = @U_City,
@U_Zip = @U_Zip,
@U_State = @U_State,
@U_Country = @U_Country,
@Press = 'yes',
@password = @password,
@datenow = @datenow ,
@mstat = @mstat,
@User_ID = @U_ID OUTPUT

EXEC sp_insert_Resource_U_opt_1
@U_ID_1 = @U_ID,
@num_employ_2 = @num_employ_2,
@Num_fac_3 = @Num_fac_3,
@Ind_group_4 = @Ind_group_4

EXEC sp_insert_Aggman_User_Questions_1
@U_ID_1 = @U_ID,
@U_Company_Products_2 = @U_Company_Products_2,
@U_Company_Job_Description_3 = @U_Company_Job_Description_3,
@U_Company_Purchase_4 = @U_Company_Purchase_4,
@U_Date_5 = @U_Date_5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources