 |
|
|
|
I need to create a user from some info entered into a form. Then I need to take the UserId that is returned by the AddUser SPROC and populate another table with some data and the UserId.
I have a SPROC that pulls in the required data from parameters and runs Exec AddUser that returns the UserId.
Then I have another Insert that puts data into a table. But I cannot figure out what to do with the UserId that is returned from Exec AddUser. When I run in a SQL Window I can see the new user is added and the UserId is returned. But it is not getting passed into the insert statement for the next table. I have tried to Set the NewUserId = @UserId and all kinds of crazy things. But it just is not working. I thought this would be simple but I cannot find anything online to help.
Here is one try of code. Both the AddUser and Insert work, it just does not get the UserId to put into the Insert statement.
ALTER
PROCEDURE [dbo].[XMP_AskAQuestion_SPROC] -- Add the parameters for the stored procedure here
@PortalID
int,
@Username
nvarchar(100),
@FirstName
nvarchar(50),
@LastName
nvarchar(50),
@AffiliateId
int,
@IsSuperUser
bit,
@Email
nvarchar(256),
@DisplayName
nvarchar(100),
@UpdatePassword
bit,
@Authorised
bit,
@InsState
nvarchar(100),
@InsType
nvarchar(100),
@InsQuestion
AS
BEGIN
ntext
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Exec
@PortalID
@Username
@FirstName
@LastName
@AffiliateId
@IsSuperUser
@Email
@DisplayName
@UpdatePassword
@Authorised
THIS IS WHERE I DON"T KNOW WHAT TO DO @NewUserId = Scope_Identity()
INSERT
INTO XMP_AskAQuestion
(
UserId, InsState, InsType, Email, InsQuestion)
VALUES
(@NewUserId, @InsState, @InsType, @Email, @InsQuestion)
-- Need to insert into AF
END
AddUser, , , , , , , , ,
|
| |
|
|
 | |
|
|
 |
www.tressleworks.ca
Joined: 4/16/2004
Posts: 493
|
|
|
Take a look at this blog entry (here) about adding New Users and review how the information is passed from statement to statement.. You may find something that will help.
If still having issues .. then re-post here and I will try to help.
Paul.
|
| |
|
|
| |
|
|
|
Thanks Paul. Since the AddUser SPROC doesn't use a RETURN or OUTPUT but only a simple select, I was told to use a temp table to store the userid so that I could pass it along to my other INSERT. That worked great. Here is what DID work. Leave the parameters open in the SPROC and run this in a query window.
EXEC
[dbo].[XMP_AskAQuestion_SPROC] = '0', = 'cliff_hammockhr@yahoo.com', = 'Cliff', = 'Hammock', = NULL , = '0', = 'cliff_hammock@yahoo.com', = 'Cliff Hammock', = '0', = '1',
@InsState
@InsType
@InsQuestion
= 'Alaska', = 'Health Insurance', = 'Here is my question',
@Referrer
= 'my referrer',
@HostAddress
= '68.106.225.336',
@Agent
= 'mozilla',
@Browser
= 'IE7'
Now that I have the SQL working I am trying to use XMod Pro to run the SPROC and pass in the parameters from a user form. I kept getting this error, which made me think it was an XMod Pro issue.
Msg 8114, Level 16, State 5, Procedure AddUser, Line 0
Error converting data type nvarchar to int.
But I went back into SQL and tried to take the parameters out of my exec statement and hard code them into the AddUser part of my SPROC and I got the same error.
Exec @userid = AddUser
@Username ,
@FirstName ,
@LastName ,
@IsSuperUser ,
@Email ,
@DisplayName ,
@UpdatePassword ,
@Authorised,
@PortalID ='0',
@AffiliateId = '1' --I also tried = NULL with same error.
So now it looks like it is something deeper in the AddUser SPROC. Any ideas would be appreciated.
Here is my current full SPROC that does work when executed with all parameters passed in as shown up at the top.
USE
GO
[jakeviznerinsure]
/****** Object: StoredProcedure [dbo].[XMP_AskAQuestion_SPROC] Script Date: 07/18/2009 18:15:47 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Cliff Hammock
-- Create date: 18 July 2009
-- Description: Create UserId and return it to
-- post to forum, then insert into
-- XMP_AskAQestion table
-- =============================================
[dbo].[XMP_AskAQuestion_SPROC]
-- Add the parameters for the stored procedure here
@PortalID
@Username
@FirstName
@LastName
@AffiliateId
@IsSuperUser
@Email
@DisplayName
@UpdatePassword
@Authorised
@InsState
@InsType
@InsQuestion
@Referrer
@HostAddress
@Agent
@Browser
int , nvarchar ( 100 ), nvarchar ( 50 ), nvarchar ( 50 ), int , bit , nvarchar ( 256 ), nvarchar ( 100 ), bit , bit , nvarchar ( 100 ), nvarchar ( 100 ), ntext, nvarchar(500), nvarchar(50), nvarchar(500), nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET
NOCOUNT ON ;
-- Insert statements for procedure here
DECLARE
@NewUsers TABLE ( userid int ) --creates a temporary table
DECLARE
@userid int
INSERT
@NewUsers ( userid )
Exec
@PortalID
@Username
@FirstName
@LastName
@AffiliateId
@IsSuperUser
@Email
@DisplayName
@UpdatePassword
@Authorised
@userid = AddUser , , , , , , , , ,
select
@userid = userid FROM @NewUsers
SELECT
* FROM Users
WHERE
UserID = @userid
INSERT
INTO XMP_AskAQuestion
(
UserId, InsState, InsType, Email, InsQuestion, Referrer, HostAddress, Agent, Browser)
VALUES
(@UserId, @InsState, @InsType, @Email, @InsQuestion, @Referrer, @HostAddress, @Agent, @Browser)
END
@PortalId
@Username
@FirstName
@LastName
@AffiliateId
@IsSuperUser
@Email
@DisplayName
@UpdatePassword
@Authorised
|
| |
|
|
| |