want to return name from a stored procedure by its id

Posted by Santosh.Choudhury under Sql Server on 4/12/2012 | Points: 10 | Views : 1330 | Status : [Member] | Replies : 7
ALTER proc [dbo].[getTriggerNamesbyId]
@id int,
@returnval nvarchar(500) output
as
begin
set @returnval= (select TriggerPointDescription from ClaimTriggerInfo where TriggerPointID=@id )
return (@returnval)
end
Above is my stored procedure but when i pass valu it give me errror such as (Msg 245, Level 16, State 1, Procedure getTriggerNamesbyId, Line 12
Conversion failed when converting the nvarchar value 'sfdajfr' to data type int.)
then how it possible




Responses

Posted by: Kavi.Sirius on: 4/12/2012 [Member] Starter | Points: 25

Up
0
Down
hi,
u can use this code,

ALTER proc [dbo].[getTriggerNamesbyId]
@id int,
@returnval nvarchar(500) output
as
begin
set @returnval= (select TriggerPointDescription from ClaimTriggerInfo where TriggerPointID=@id )
print (@returnval)
end

it doest show the conversion failed error

Regards
Kavi.n

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Leila on: 4/12/2012 [Member] Starter | Points: 25

Up
0
Down
Store procedure did not return values, functions in sql do.

Thanks,
Leila

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somasundaram on: 4/12/2012 [Member] Starter | Points: 25

Up
0
Down
Create stored procedure:
Alter proc temp
@id int,
@returnval nvarchar(50)=null output
as
begin
set @returnval = (select nvrusername from tblUser where intSno=@id)
end

In Code Behind:
con = new SqlConnection(ConfigurationManager.AppSettings["cons"]);
con.Open();
cmd = new SqlCommand("temp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", 2);

SqlParameter param = new SqlParameter();
param.Direction = ParameterDirection.Output;
param.DbType = DbType.String;
param.Size = 50;
param.ParameterName = "@returnval";
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery();
Id = Convert.ToString(cmd.Parameters["@returnval"].Value);
Response.Write(Id);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}


Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 4/12/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi,
You are passing 'sfdajfr', which is a string value but your stored procedure expecting an interger value @id int

Thanks,
Rajni Shekhar

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/8/2012 [Member] Starter | Points: 25

Up
0
Down
CREATE PROCEDURE returnname
-- Add the parameters for the stored procedure here
(
@id int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select username from Tablename where id=@id

END
GO

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Meet1424 on: 6/9/2012 [Member] Starter | Points: 25

Up
0
Down
@ somasundaram, sir why are you passing string to a parameter with data type int??
sfdajfr is a string and can not be converted into int.

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Meet1424 on: 6/9/2012 [Member] Starter | Points: 25

Up
0
Down
and yes you can not return value from procedure.

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response