inserting numeric nvarchar data w/ SP

[gtjr921]

I have a SP that gets data from parameters in Asp.net the .net form passes data from an excel spreadsheet.
This all works except that when it inserts any of the fields that contain numbers they end up in sql looking like 9.37123e+009 or 4.581e+008
I have tried cast and convert to no avail.
The three fields I am trying to insert numeric data to all have nvarchar(50)
I believe SQL is seeing the the incoming data as float
even though i have the data in excel as general or text.
here is my sp

ALTER PROCEDURE [dbo].[mySP] 
@FirstName nchar(255),
@HomeAddress nvarchar(256),
@HomeCity nvarchar(256),
@HomeState nvarchar(256),
@HomeZIP nvarchar(255),
@HomePhone nvarchar(50),
@RoleName varchar(256)
AS
Begin
	Set NoCount on
	Declare @TelephoneID int 
	
	Select @TelephoneID = Count(TelephoneID)+1  from app_TelephoneDetails
	
	Insert into app_TelephoneDetails (TelephoneID, Telephone,
FirstName, RoleName,
HomeAddress,HomeCity,
HomeState,HomeZIP,
HomePhone,LastModified,
 StatusCode, LastUser,Active)
	values
	(@TelephoneID, CAST(@HomePhone AS nvarchar(50)), 
@FirstName,@RoleName,
@HomeAddress,@HomeCity,
@HomeState,CAST(@HomeZip AS nvarchar(50)),CAST(@HomePhone AS nvarchar(50)),
 { fn NOW() }, 'Open', 'admin', -1)             
	
End

 

[SQLDenis]

Why 9.37123e+009 or 4.581e+008 is stored is probably what is passed in can you show us the proc call

>>I believe SQL is seeing the the incoming data as float
are you passing in a float and SQL is doing an implicit conversion?

Now for the interesting stuff
----------------------------------

how many ways to mess up a proc

1 @FirstName nchar(255),

great lets pad every name with 200 + spaces this will be great for IO and rows per page

2) { fn NOW() }

why are you using odbc date functions instead of CURRENT_TIMESTAMP or GETDATE()

3) CAST(@HomePhone AS nvarchar(50)),

This is already nvarchar, why do you need nvarchar for number anyway there is no need for the 2 bytes per character overhead

4) Select @TelephoneID = Count(TelephoneID)+1 from app_TelephoneDetails

great what happens when 2 users execute the proc at the same time, lookup identity. you also don't have a transaction in this proc


5) @HomeState nvarchar(256),
is this statecode or statename? if it is code why is it nvarchar 256?

6) why is this all in one table? ever heard of 3Nf (Third normal form)

This is a horrible design and a recipe for headaches down the line

good luck




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions