September 29, 2008 at 5:58 pm
Hi:
I have writtern a Stored procedure in which have taken a Bigint value and insert it into another table.
while running the query i met an error.I have mentioned my Stored procedure and the error i have met below.
Stored Procedure:
ALTER PROCEDURE [dbo].[DAMS_SP_InsertUserDetails]
@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)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlinsert nvarchar(100)
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
(cast (@sqlinsert as Bigint) ,@FirstName,@LastName,@DateOfBirth ,@StreetAddress1 ,@StreetAddress2 ,@City,@State ,@CountryId ,@Email,
@PhoneNo,@MobileNo,@FaxNo ,@EmailDigestFrequency,@AreaOfInterest ,@InvoiceDeliveryMethod )
END
Error:
System.Data.SqlClient.SqlException: Could not find stored procedure 'SELECT UserId FROM DAMS_Tbl_UsersLogin WHERE UserName='jhon''.
Error converting data type nvarchar to bigint.
Since its very very urgent
Can any one send me query or give me a suggestion to solve this issue.
Thanks
September 30, 2008 at 3:01 am
suresh.s (9/29/2008)
Hi:I have writtern a Stored procedure in which have taken a Bigint value and insert it into another table.
while running the query i met an error.I have mentioned my Stored procedure and the error i have met below.
EXEC @sqlinsert
INSERT INTO DAMS_Tbl_RegisteredUsers(UserId,FirstName,LastName,DateOfBirth ,StreetAddress1 ,StreetAddress2 ,
City,State ,CountryId ,Email ,PhoneNo,MobileNo,FaxNo ,EmailDigestFrequency,AreaOfInterest ,InvoiceDeliveryMethod )VALUES
(cast (@sqlinsert as Bigint) ,@FirstName,@LastName,@DateOfBirth ,@StreetAddress1 ,@StreetAddress2 ,@City,@State ,@CountryId ,@Email,
@PhoneNo,@MobileNo,@FaxNo ,@EmailDigestFrequency,@AreaOfInterest ,@InvoiceDeliveryMethod )
Error:
System.Data.SqlClient.SqlException: Could not find stored procedure 'SELECT UserId FROM DAMS_Tbl_UsersLogin WHERE UserName='jhon''.
Error converting data type nvarchar to bigint.
1.Your could not find stored procedure error occurs because you have used exec @sqlinsert without brackets.Use it within brackets as exec (@sqlinsert).
2.Are you sure you want to convert the @sqlinsert statement itself into a big int value or the output of the statement to a big int value?Seems that you are trying to insert the result of the query but rather the select statement itself is getting casted and so the error.
September 30, 2008 at 3:16 am
Hi Suresh
You're very nearly there, just a couple of small changes...
[font="Courier New"]DECLARE @UserId BIGINT
SELECT @UserId = UserId
FROM DAMS_Tbl_UsersLogin
WHERE UserName = @UserName
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 )
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 1, 2008 at 2:59 pm
hi :
First of all i am very thankful to u.it works greate.Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply