how to exec stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to exec stored procedure


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

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.
One of the columns length is not enough. You need to increase it and try Madhivanan Failing to plan is Planning to fail
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.
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
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>
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>
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.
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)
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.

]]>