Insert table with SubQuery - SQL Server Forums

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

.NET Inside SQL Server (2005)

Insert table with SubQuery

Author

Topic

suresh_dotnet
Starting Member

6 Posts

Posted - 2008-09-29 : 15:18:26

Hi: I am using

Login,Userdetails tables in which UserID is common for both tables.UserId is Primary key for Login .UserId is not a Foreign Key for Userdetails table due to it refers more than one table.

Now I have to take UserId from Login table then insert into Userdetails table along with some other parameters.I have writtern procedure but i got some error.Procedure and the Error has Mentioned below.

Procedure:

ALTER PROCEDURE [dbo].[DAMS_SP_InsertUserDetails]

AS

BEGIN

DECLARE @sqlinsert varchar(100),

@UserId as bigint,
@UserName as varchar(25),
@FirstName as varchar(100),
@LastName as varchar(100),
@DateOfBirth as datetime,
@StreetAddress1 as varchar(1000),
@StreetAddress2 as varchar(1000),
@City as varchar(250),
@State as varchar(250),
@CountryId as int,
@Email as varchar(250),
@PhoneNo as varchar(25),
@MobileNo as varchar(25),
@FaxNo as varchar(25),
@EmailDigestFrequency as char(25),
@AreaOfInterest as int,
@InvoiceDeliveryMethod as varchar(25)

SET NOCOUNT ON;

SET @sqlinsert=''
SET @sqlinsert=@sqlinsert+'SELECT UserId FROM DAMS_Tbl_UsersLogin WHERE UserName='''+@UserName+''''
EXEC @sqlinsert

INSERT INTO DAMS_Tbl_RegisteredUsers(UserId,FirstName,LastName,DateOfBirth ,StreetAddress1 ,StreetAddress2 ,

City,State ,CountryId ,Email ,PhoneNo,MobileNo,FaxNo ,EmailDigestFrequency,AreaOfInterest ,InvoiceDeliveryMethod )VALUES

(@sqlinsert ,@FirstName,@LastName,@DateOfBirth ,@StreetAddress1 ,@StreetAddress2 ,@City,@State ,@CountryId ,@Email,

@PhoneNo,@MobileNo,@FaxNo ,@EmailDigestFrequency,@AreaOfInterest ,@InvoiceDeliveryMethod )

END

Error:
Generally I got the Error
" Parameter is not supplied"

Can any one send me query to solve this issue plz. its very urgent.

suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:41:20

How were you executing the sp?Also why are you using dynamic sql? there seems to be no need of using it in scenario above.

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 11:56:12

You are inserting the @SQLInsert parameter in place for an Userid, i dont know the reason you are doing it purposefully or, un knowingly. Since Userid is an inetger field, this will give you an error.

INSERT INTO DAMS_Tbl_RegisteredUsers(UserId,FirstName,LastName,DateOfBirth ,StreetAddress1 ,StreetAddress2 ,

City,State ,CountryId ,Email ,PhoneNo,MobileNo,FaxNo ,EmailDigestFrequency,AreaOfInterest ,InvoiceDeliveryMethod )VALUES

(@UserId ,@FirstName,@LastName,@DateOfBirth ,@StreetAddress1 ,@StreetAddress2 ,@City,@State ,@CountryId ,@Email,

@PhoneNo,@MobileNo,@FaxNo ,@EmailDigestFrequency,@AreaOfInterest ,@InvoiceDeliveryMethod )

END

Please replace your insert statement with the one above and try updating the SP

Regards
Angel

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-30 : 04:46:18

quote:
Originally posted by ursangel

INSERT INTO DAMS_Tbl_RegisteredUsers(UserId,FirstName,LastName,DateOfBirth ,StreetAddress1 ,StreetAddress2,City,State ,CountryId ,Email ,PhoneNo,MobileNo,FaxNo ,EmailDigestFrequency,AreaOfInterest ,InvoiceDeliveryMethod )VALUES
(@UserId ,@FirstName,@LastName,@DateOfBirth ,@StreetAddress1 ,@StreetAddress2 ,@City,@State ,@CountryId ,@Email,@PhoneNo,@MobileNo,@FaxNo ,@EmailDigestFrequency,@AreaOfInterest ,@InvoiceDeliveryMethod )

Make sure to set the @UserId with the select statement. As Visakh mentioned there is no need to use dynamic sql to get the user id. Use a normal select statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources