Ошибка при преобразовании типа данных nvarchar к bigint

SqlZim already gave you a good method to avoid the error in his answer. However, in the question and in comments you seem curious as to why one query throws an error and the other does not. I am able to reproduce your issue:

CREATE TABLE dbo.X_BIGINT_TABLE (ID BIGINT NOT NULL);

INSERT INTO dbo.X_BIGINT_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_NVARCHAR_TABLE (ID_NV NVARCHAR(10) NOT NULL);

INSERT INTO dbo.X_NVARCHAR_TABLE WITH (TABLOCK)
SELECT TOP (999) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
FROM master..spt_values

UNION ALL

SELECT 'ZOLTAN';

This query works fine:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE ISNUMERIC(NV.ID_NV) = 1;

This query throws an error:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Msg 8114, Level 16, State 5, Line 25

Error converting data type nvarchar to bigint.

The SQL Server query optimizer can reorder elements of a query as it sees fit to try to find a query plan with a good enough estimated cost, as long as the changes do not affect the final results of the query. To illustrate the concept lets walk through one possible way the second query can be refactored. To be clear, this is not the actual step-by-step process that the query optimizer goes through for this example. Start with the query:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Push down the predicate:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1
) ZZ;

The derived table is no longer needed so get rid of that:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1

We know that BI.ID = NV.ID_NV so we can apply the filter on Z.ID to NV.ID_NV as well:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500

The join no longer needs to be implemented as INNER JOIN because we are filtering down to a single value for both join columns. We can rewrite as a CROSS JOIN:

SELECT * 
FROM 
(
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    WHERE BI.ID = 500
) 
CROSS JOIN 
(
    SELECT *
    FROM dbo.X_NVARCHAR_TABLE NV
    WHERE ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500
);

If we look at the query plan for the second query we can tell that the end result is very similar to the final transformed query:

Here is the text of the filter predicate for reference:

CONVERT_IMPLICIT(bigint,[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0)=(500) 
AND isnumeric(CONVERT_IMPLICIT(varchar(20),[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0))=(1)

If SQL Server evaluates the CONVERT_IMPLICIT part of the predicate before the isnumeric part then we get an error.

As a general rule, avoid relying on implied order of operations when writing SQL queries. You may have a query that works well today but starts to throw errors if data is added to the table or if a different query plan is chosen. There are, of course, exceptions (kind of). In practice, you will usually see the different parts of a CASE statement to evaluate in the order that you’ve written them, but even then it’s possible to get errors that you weren’t expecting. You can also add a superfluous TOP to parts of your query to encourage a certain order of operations. Consider the following query:

SELECT *
FROM (
    SELECT TOP (9223372036854775807) *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
    ) ZZ 
WHERE ZZ.ID = 500;

You and I know that the TOP will not change the results of the query, However, there is not a guarantee to the optimizer that the derived table won’t return more than 9223372036854775807 rows so it must evaluate the TOP. Technically, in that query we ask for the first 9223372036854775807 rows and then we want to filter out rows with an ID different from 500. Pushing the ID = 500 predicate down to the derived table could change the results so SQL Server will not do that. In this example, the query executes without an error and the filtering is done at the very end:

ABhinav i already check my parameters and i didnt put a varchar value can you please see this program as well to check..

Dim conn As New SqlConnection("Data Source=192.168.0.4;Initial Catalog=CYBERYA;Persist Security Info=True;User ID=cyberya;Password=Piso4minutes;MultipleActiveResultSets=True")

        

        Dim cmdtest As New SqlCommand("Populatecsv", conn)
        cmdtest.CommandType = CommandType.StoredProcedure
        conn.Open()
        cmdtest.Connection = conn
        
        cmdtest.Parameters.Clear()
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", "srn"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Id", "Id"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@status", "status"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CustomerName", "customername"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@HomeContact", "homecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BusinessContact", "businesscontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@MobileContact", "mobilecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@LotHouseNo", "lothouseno"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@RoomUnitStall", "roomunitstall"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgFloor", "bldgfloor"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgName", "bldgname"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Street", "street"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Subdivision", "subdivision"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Barangay", "barangay"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CityMunicipality", "citymunicipality"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Province", "province"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Region", "region"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Package", "package"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@PromoCode", "promocode"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ApplicationDate", "applicationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@EndorsedDate", "endorseddate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@paymenttype", "paymenttype"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@scheduledate", "scheduledate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@loanstatus", "loanstatus"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ActivationDate", "activationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcdelivery", "pcdelivery"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@nopcavailed", "nopcavailed"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@datepcpickup", "datepcpickup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcissuance", "pcissuance"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@salesagent", "salesagent"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Coordinator", "Coordinator"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesChannel", "saleschannel"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesGroup", "salesgroup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CreatedBy", "CreatedBy"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@timestamp", "timestamp"))
        

——————————-stored procedure——————————

ALTER procedure [dbo].[Populatecsv]
@ID BIGINT,
@SRCode BIGINT,
@SRNumber BIGINT,
@Status NVARCHAR (MAX),
@CustomerName NVARCHAR (MAX),
@HomeContact NVARCHAR (MAX),
@BusinessContact NVARCHAR (MAX),
@MobileContact NVARCHAR (MAX),
@LotHouseNo NVARCHAR (MAX),
@RoomUnitStall NVARCHAR (MAX),
@BldgFloor NVARCHAR (MAX),
@BldgName NVARCHAR (MAX),
@Street NVARCHAR (MAX),
@Subdivision NVARCHAR (MAX),
@Barangay NVARCHAR (MAX),
@CityMunicipality NVARCHAR(MAX),
@Province NVARCHAR (MAX),
@Region NVARCHAR (MAX),
@Package NVARCHAR (MAX),
@PromoCode NVARCHAR (MAX),
@PaymentType Nvarchar (MAX),
@ApplicationDate DATETIME,
@EndorsedDate DATETIME,
@PaymentDate DATETIME,
@ScheduleDate DATETIME,
@ActivationDate DATETIME,
@PCDelivery NVARCHAR (MAX),
@NoPCAvailed BIGINT,
@datepcpickedup DATETIME,
@pcissuance nvarchar (MAX),
@SalesAgent NVARCHAR (MAX),
@Coordinator NVARCHAR (MAX),
@SalesChannel NVARCHAR (MAX),
@SalesGroup NVARCHAR (MAX),
@CreatedBy NVARCHAR (MAX),
@LoanStatus NVARCHAR (MAX),
@TimeStamp DATETIME,
@SRN BIGINT OUTPUT
AS
BEGIN
    INSERT INTO SRN (ID,SRCode,SRNumber,Status,CustomerName, HomeContact,BusinessContact,MobileContact,LotHouseNo,RoomUnitStall,BldgFloor,BldgName,Street, Subdivision,Barangay,CityMunicipality,Province,Region ,Package, PromoCode,PaymentType,ApplicationDate,EndorsedDate,PaymentDate,ScheduleDate,ActivationDate,PCDelivery ,NoPCAvailed,datepcpickedup, pcissuance,SalesAgent ,Coordinator,  SalesChannel,SalesGroup, CreatedBy,LoanStatus,TimeStamp )
    VALUES (@ID, @SRCode,@SRNumber,@Status,@CustomerName,@HomeContact,@BusinessContact,@MobileContact,@LotHouseNo,@RoomUnitStall,@BldgFloor,@BldgName,@Street,@Subdivision,@Barangay,@CityMunicipality,@Province,@Package,@PromoCode,@PaymentType,@ApplicationDate,@EndorsedDate ,@PaymentDate,@ScheduleDate,@ActivationDate,@PCDelivery,@NoPCAvailed,@datepcpickedup,@pcissuance,@SalesAgent,@Coordinator,@SalesChannel ,@SalesGroup,@CreatedBy, @LoanStatus, @TimeStamp  );
    SELECT @SRN= SCOPE_IDENTITY();
END

Johan Bijnens

SSC Guru

Points: 135002

этот код дает мне ошибку:

error in converting datatype nvarchar to bigint

это мои значения таблицы:

   @customer_first_name nvarchar (50), @customer_last_name nvarchar (50), @customer_address nvarchar (max), @gender nchar (10), @date_of_birth datetime, @customer_email_id varchar (50), @customer_occuption varchar (50), @customer_occuption varchar (50), @customer_occuption varchar (50), @nominee_name nchar (50), @customer_id nvarchar (50), @account_type nvarchar (50), @account_number nvarchar (50), @transfer_access_code nvarchar (50), @account_balance Bigint, @pin_code BigInt, @nationality, nvarchar (XNUMX) @password nvarchar (XNUMX)
    

lblAmount.Text = HFamount .Value ;
int balamount = Convert.ToInt32(lblAmount.Text);
int Cpincode = Convert.ToInt32(txtpincode.Text);

long phone = Convert.ToInt64(txtphone.Text);
string cust_id = CUSTOMER_ID_GENERATOR();
string sqlquery = "INSERT INTO customer_details values(@customer_first_name,@customer_last_name,@customer_address,@gender,@date_of_birth,@customer_email_id,@customer_occuption,@customer_phone_number,@nominee_name,@customer_id,@account_type,@account_number,@transfer_access_code,@account_balance,@pin_code,@nationality,@password)";
string strconstring = ConfigurationManager.ConnectionStrings["ONLINE_BANKING2_ConnectionString"].ConnectionString;
SqlConnection mycon = new SqlConnection(strconstring);
mycon.Open();
SqlCommand  cmdRegister= new SqlCommand(sqlquery,mycon);
cmdRegister.Parameters.Add(new SqlParameter("@customer_first_name",txtcustname.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@customer_last_name",txtcustlastname.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@customer_address",txtAddress.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@gender", rbtngender.SelectedItem.Value.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@date_of_birth", txtDOB.Text));
cmdRegister.Parameters.Add(new SqlParameter("@customer_email_id", txtemail.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@customer_occuption",  txtcustoccupation.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@customer_phone_number",  phone));
cmdRegister.Parameters.Add(new SqlParameter("@nominee_name",  txtnominee.Text.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@customer_id",  cust_id));
cmdRegister.Parameters.Add(new SqlParameter("@account_type",  accType.SelectedItem.Value.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@account_number",  ACCOUNT_NUMBER_GENERATOR()));
cmdRegister.Parameters.Add(new SqlParameter("@transfer_access_code",Transfer_code()));
cmdRegister.Parameters.Add(new SqlParameter("@account_balance",  balamount));
cmdRegister.Parameters.Add(new SqlParameter("@pin_code",  Cpincode));
cmdRegister.Parameters.Add(new SqlParameter("@nationality", DropCountry.SelectedItem.Value.ToString()));
cmdRegister.Parameters.Add(new SqlParameter("@password",txtpasswd2.Text.ToString()));
cmdRegister.ExecuteNonQuery();
mycon.Close();

Пожалуйста, помогите мне

только просмотры при ответе