CODEWITHSUNDEEP
sqlsql-servert-sql
erhan urun
erhan urun

Reputation: 115

Converting nvarchar to bigint in sql server

I'm trying to convert nvarchar(16) to bigint but receives an error as follows

"Arithmetic overflow error converting expression to data type int."

How can I fix it this is my sp

Create Procedure [dbo].[GenerateAccountNo]

As 

Begin
declare @tmp bigint
set @tmp =(CONVERT(bigint,'1111111111111111'))
return @tmp

End

And call procedure

DECLARE @returnvalue bigint

 EXEC @returnvalue = GenerateAccountNo

 select @returnvalue

Upvotes: 2

Views: 2347

Answers (4)

erhan urun
erhan urun

Reputation: 115

thank u for your answers. I fix the problem as follows...

Create Procedure [dbo].[GenerateAccountNo]

  As 
    Begin
    declare @tmp bigint
    set @tmp =(CONVERT(bigint,'1111111111111111'))
    select @tmp

    End

and calling the procedure as follows...

            SqlCommand cmd = new SqlCommand("GenerateAccountNo", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter parm = new SqlParameter("@accountNo", SqlDbType.NVarChar,16);
            parm.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parm);
            cmd.ExecuteNonQuery();

Upvotes: 0

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

I haven't written C# in a LONG time, but can't you use output parameters as shown below? That said, a user defined function as suggested by Lukos might be a better option depending on the actual code you need in your method.

create procedure [dbo].[generateaccountno] @account_number [bigint] = null output
as
  begin
      set @account_number =( convert(bigint, '1111111111111111') );
  end;
go
declare @account_number [bigint];
execute [dbo].[generateaccountno] @account_number = @account_number output;
select @account_number as [@account_number];

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

From MSDN

Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

You are returning the status(INT) from a Stored Procedure. This would be the execution result code (success or failure). If you want to return results from a Stored Procedure just use SELECT within the SP.

CREATE PROCEDURE [dbo].[GenerateAccountNo]
As 
BEGIN
    SELECT CONVERT(BIGINT,'1111111111111111')
END

Upvotes: 2

Lukos
Lukos

Reputation: 1852

The return value from a stored proc is INT, not BIGINT. Stored procs are designed for data selection. You should use a FUNCTION in this case instead to return a scalar value that is not related to a table.

Upvotes: 3

Related Questions