SQL Server Performance

how to exec stored procedure

Discussion in 'Getting Started' started by dhan, May 9, 2007.

  1. dhan New Member


    Hai

    I m new to sql server. I want to execute a stored procedure.

    Which is:

    create procedure usp_displayallusers @eno int, @ename varchar,@mno int as insert into

    emp1 values(@eno,'@ename',@mno)

    I successfully run the above.

    When i executes

    exec usp_displayallusers 1,'p',2

    The execution will shoe err like

    "String or binary data would be truncated.

    The statement has been terminated. "


    What will i do to run ?


    Thanks in advance
  2. Adriaan New Member

    First of all, why is your procedure called "display all users", eventhough it actually inserts a new row into the emp1 table?

    Now the problem with this ...

    insert into emp1 values(@eno,'@ename',@mno)

    ... is that you are inserting the string '@ename' as the name, instead of the value contained in the @ename variable, which would have been 'p'.

    I would guess that the name '@ename' is too long for the column in your emp1 table. To be honest, 6 characters are too short for any name, I would imagine.
  3. Madhivanan Moderator

    One of the columns length is not enough. You need to increase it and try

    Madhivanan

    Failing to plan is Planning to fail
  4. satya Moderator

    In other case if you are sure about no overvalued columns then you can include the statement SET ANSI_WARNINGS OFF, and SQL will truncate in silence and not raise an error.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. FrankKalis Moderator

    Please post the code of your proc. This error indicates that a columns seems to be too narrow to take all the data you want to be stored.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  6. satya Moderator

    This is a sensible move of a thread by you ([<img src='/community/emoticons/emotion-1.gif' alt=':)' />] [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  7. FrankKalis Moderator

    I knew you would comment on that! [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. Adriaan New Member

    The problem is still that the INSERT statement included the name of the variable in a quoted string, instead of the variable name without the quotes.

    The written-out variable name was too long for the column, but the value he was hoping to insert ('p') certainly wasn't.
  9. DilliGrg Member

    quote:Originally posted by dhan


    Hai

    I m new to sql server. I want to execute a stored procedure.

    Which is:

    create procedure usp_displayallusers @eno int, @ename varchar,@mno int as insert into

    emp1 values(@eno,'@ename',@mno)

    I successfully run the above.

    When i executes

    exec usp_displayallusers 1,'p',2

    The execution will shoe err like

    "String or binary data would be truncated.

    The statement has been terminated. "


    What will i do to run ?


    Thanks in advance


    Do you mean to do this?



    alter procedure usp_displayallusers
    @eno int
    , @ename varchar(10)
    , @mno int

    as

    insert into emp1(eno, ename, mno)
    values(@eno,@ename,@mno)

    GO
    exec usp_displayallusers 1,'p',2
    GO





    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  10. MohammedU New Member

    I agree with Adriaan solution...

    The following code will repro the probelm...


    create table emp1 (n int, name Varchar(5), n1 int)
    go
    create procedure usp_displayallusers @eno int, @ename varchar,@mno int as
    insert into
    emp1 values(@eno, '@ename' ,@mno)
    go
    exec usp_displayallusers 1,'p',2
    go
    alter procedure usp_displayallusers @eno int, @ename varchar,@mno int as
    insert into
    emp1 values(@eno, @ename ,@mno)
    go
    exec usp_displayallusers 1,'p',2
    go
    drop table emp1



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page