help creating a Stored Procedure

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..

I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null)go

Here is what i have so far for the stored procedure

create procedure getcustomProfile @.UserIduniqueidentifierasSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoifnot exists (select *from dbo.aspnet_Userswhere UserId = @.UserId)BEGINDeclare @.UserIduniqueidentifier update (dbo.content_Profile)set UserId = @.UserIdwhere UserId = @.UserIdEndELSEBEGIN INSERT INTO dbo.custom_Profile (UserId) VALUES (@.UserId)END

|||

Hi,

I'm sure you can achieve what you want from a stored procedure, but there are easier ways. The article at:

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

This shows how to create a table using SMO.

Hope this helps.

Paul

|||Thats cool something to look into later, but for now i'm just working on studying the creation of Stored Procedures. Won't learn how to create them if a program does most of the work for me, why i prefer to do it this way for now|||

Hi,

No problem with that. Mind you, think i mis-read it anyway - thought you needed to create a table from a stored procedure. You don't actually say what is wrong with what you've done so far. I might be wrong, but I don't think you need the line 'Declare @.UserIduniqueidentifier'as you have already passed this in as a parameter value.

Let me know what appears to be going wrong and I'll try and help further.

Paul

|||

I was just trying out the table and sp in my site, and when i create a new user account a new row isn't created in the column for the table..Trying to get the stored procedure to take the UserId from the aspnet_Users table example (59afcb85-c20c-4937-8ab9-a44a57e22ce0). Than Insert that UserId into my table custom_Profile (Column UserId) and do this for each new user account..

|||forgot to put in the T-SQL ...lol here you go

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,SexualOrientationnvarchar(20)null,Heightnchar(10)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null,Circumcisednvarchar(20)null)gocreate procedure getcustomProfile @.UserIduniqueidentifier asSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoBEGININSERT dbo.custom_Profile (UserId)VALUES (@.UserId)SELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdEND

|||

Oh and when i remove the Declare @.UserId uniqueidentifier i get a error::

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@.UserId".

|||

Hi,

Apologies, I misunderstood. The problem is passing of the uniqueidentifier - this will cause the SP to fail. You should change this to an nvarchar field. I messed around a little and the following SP will loop through all of the users:

CREATE procedure [dbo].[getcustomProfile] @.UserIdnvarchar(150)ASDECLARE UserInsertCURSORKEYSETFOR SELECT UserIDFROM dbo.aspnet_UsersDECLARE @.Usernvarchar(150)OPEN UserInsertFETCH NEXT FROM UserInsertINTO @.UserWHILE (@.@.fetch_status = 0)BEGININSERT dbo.custom_Profile (UserId)VALUES (@.User)FETCH NEXT FROM UserInsertINTO @.UserENDCLOSE UserInsertDEALLOCATE UserInsert

This does work, and you can use it as your starting point for the actual SP you need to call each time from your form.

Hope it helps,

Paul

|||So i need to call the SP from the code behind correct? for it to insert the UserId into the table upon creation of a new user account|||

Your logic in your original stored proc is incorrect. You have

IF NOT EXISTS(...)

UPDATE

ELSE

INSERT

If you think about it, how can you update something that doesnt exist? It should be the other way.

IF NOT EXISTS(...)

INSERT

ELSE

UPDATE

Fix your proc according to the logic above and post back if you have any issues.

|||

Hi,

Yes, you should call the SP from BLL once you've gathered together the results of your form. I'm assuming from the way you set out the SP that you're intending to do the User insert first and then then pass in the new UserID (with other parameters) - you should be setting up an output parameter from your Add_User SP. You actually don't need your If Exists part of the SP because you can check that the UserID has been created before allowing the call to this SP - the fact that it is a uniqueidentifier means it won't exist already. Personally, I always keep Insert, Update and Delete SPs seperate - but that is just personal preference.

Let e know if you need anything else on this.

Cheers,

Paul

|||

The UserId is the only data set getting populated automatically upon Registration of the site, the other columns aren't populated until they have registered. Than they get Re Directed to another page where they fill in the information via FormView in the User Control Panel. Than that information is displayed by another SqlDataSource and FormView on their profile page but without the Delete,Insert,Update etc. Basically using a Content Management system for the user profile to display textual information, on their page.

I'm going to use the UserId so i can pass it into the URL for unique pages like most communities (Myspace etc etc)..

|||Why is that the aspnet_Membership,Users,UsersInRoles etc don't use CodeBehind to pass the UserId into each table? cause thats what i want to do..Pass the UserId from the aspnet_Users Table into my table|||

Hi,

You can do all of that by customising the sqlMembershipProvider. It's not as difficult as you might think - thankfully! There's a pretty good tutorial to get you started at:

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

I hope this helps.

Paul

sql