Using Numeric Datatype in Stored procedure

Posted on 2007-10-14
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
    LVL 42

    Accepted Solution

    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

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

    Expert Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now