Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Using Numeric Datatype in Stored procedure

Posted on 2007-10-14
Medium Priority
Last Modified: 2012-05-05
I have created a table with 1 column with Numeric(8,0) Datatype and a stored procedure to insert the column.
If I tried to Exec the Stored Proc, it seems like I am something wrong.

--Create Table
create table test (
      stock numeric(8,0) null

-- Stored Proc
create proc test2(
      @stock numeric(8,0)
set nocount on
if @stock = ''
      set @stock = null

-- Few examples of Executing Stored Proc
Exec test2 ''
Exec test '1'
Exec test 2

non of the Exec works....
Why is it different from int or nvarchar datatype?
Question by:erin027
  • 2
LVL 42

Accepted Solution

dqmq earned 500 total points
ID: 20075352
A numeric variable cannot contain an empty string. Therefore, the "if" statement and the first exec are no good.  The second exec will probably work, but only because the '1' gets converted to a number implicitly.  The first exec is bad because '' cannot be converted to a number.  You could do this, though
 Exec test2 NULL
 Exec test2 @stock=NULL


Author Comment

ID: 20075376
Thank you dqmq.
But how do I set up the null if user submits  it with  blank on ASP side?
LVL 42

Expert Comment

ID: 20075394
Each ASP language has a way to pass null parameters, but frankly I would avoid it.  One alternative is to pass a varchar parameter and convert it to numeric in the procedure.  Another is to create your procedure with an optional numeric parameter that defaults to null.  In that case, submit the procedure without the paremeter when you encounter blanks.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question