Error on StoredProcedure

• Gayathri.Varadarajan

  SSCrazy

  Points: 2080

  More actions

  September 30, 2008 at 3:01 am

  #878718

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 30, 2008 at 3:16 am

  #878724

  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

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• suresh.s-895438

  SSC Veteran

  Points: 290

  More actions

  October 1, 2008 at 2:59 pm

  #879746

  hi :

  First of all i am very thankful to u.it works greate.Thanks again