Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Unanswered: calling stored procedure

    Hi guys,
    I am trying to call stored procedure for insertion in hibernate and it is not working. the thing is that when i called procedure for the table which already had data then i am getting the required result. it is fetching all the data but whenever i am trying to call procedure where i want to insert values in table it is not running.

    MY PROBLEM is that i just want to ask whether any problem with my procedure. DO I NEED to decalre / initialise any parameter?? Here is my procedure


    CREATE PROCEDURE [dbo].[Customer_details_insertion]
    @customer_name text,
    @sub_customer_name text,
    @email nvarchar(100),
    @phone nvarchar(100),
    @mobile nvarchar(100),
    @city text,
    @country nvarchar(100),
    @currency nvarchar(100),
    @customer_class nvarchar(100),
    @customer_status nvarchar(100),
    @channel nvarchar(100),
    @special_instruction text,
    @date_of_joining datetime,
    @status text,
    @created_by text,
    @date_updated datetime,
    @updated_by text
    AS
    BEGIN
    INSERT INTO dbo.customer_details
    (
    customer_code,
    customer_name,
    sub_customer_name,
    email,
    phone,
    mobile,
    city,
    country,
    currency,
    customer_class,
    customer_status,
    channel,
    special_instruction,
    date_of_joining,
    status,
    created_by,
    date_updated,
    updated_by)
    VALUES
    (
    'NA',
    @customer_name,
    @sub_customer_name,
    @email,
    @phone,
    @mobile,
    @city,
    @country,
    @currency,
    @customer_class,
    @customer_status,
    @channel,
    @special_instruction,
    @date_of_joining,
    @status,
    @created_by,
    @date_updated,
    @updated_by);
    UPDATE
    dbo.customer_details
    SET
    customer_code = cast(@customer_name as varchar) + @country + @customer_class + @customer_status + CAST(@@Identity AS varchar)
    WHERE
    sequential_no = @@Identity;

    end;



    Do i need to specify any other return variable, return parameter or any other thing?
    .

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    why so many text columns? can customer_name really be 2 billion chars? I think you are using incorrect datatypes here.

    also you say it's not working - what's the errror msg?

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also if you really want customer_code to be a mashup of the other columns that way, it should be a computed column.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This abbreviated version worked.

    Drop the use of @@Identity and use SCOPE_IDENTITY() or you end up with the Id of some newly created record in another table. You should also think of validating the input (for NOT NULL, domains, ...).

    I second jezemine, you should rethink the design of that table. Random example: the column [updated_by] with data type TEXT? Are you planning to include the (wo)man's biography too, if not in this version but perhaps in a future one? Could you settle for a VARCHAR(10)? (15)? (20)?
    Code:
    DROP TABLE #customer_details
    CREATE TABLE #customer_details(
            sequential_no        int IDENTITY(1,1)PRIMARY KEY,
            customer_code    nvarchar(200)        NOT NULL,
            customer_name    NVARCHAR(25)    NOT NULL,
            sub_customer_name    NVARCHAR(25)    NOT NULL,
            email    NVARCHAR(100)    NOT NULL,
            phone    NVARCHAR(100)    NOT NULL,
            mobile    NVARCHAR(100)    NOT NULL,
            city    NVARCHAR(100)    NOT NULL
    )
    
    DROP PROCEDURE [dbo].[Customer_details_insertion]
    GO
    CREATE PROCEDURE [dbo].[Customer_details_insertion]
        @customer_name nvarchar(100),
        @sub_customer_name nvarchar(100),
        @email nvarchar(100),
        @phone nvarchar(100),
        @mobile nvarchar(100),
        @city nvarchar(100)
    AS
    BEGIN
        INSERT INTO #customer_details(
            customer_code,
            customer_name,
            sub_customer_name,
            email,
            phone,
            mobile,
            city)
        VALUES(
            'NA',
            @customer_name,
            @sub_customer_name,
            @email,
            @phone,
            @mobile,
            @city);
            
        UPDATE #customer_details
        SET customer_code = cast(@customer_name as varchar) + @city + CAST(sequential_no AS varchar)
        WHERE sequential_no = SCOPE_IDENTITY();
    
    end;
    GO
    
    EXECUTE [dbo].[Customer_details_insertion] @customer_name = 'Name', 
        @sub_customer_name = 'sub name',
        @email= 'email',
        @phone = 'phone',
        @mobile = 'mobile',
        @city = 'city'
    
    SELECT * from #Customer_details
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •