Send string Parameter to Stored Procedure

Question

I am Working on asp.net C# Project that connect to SQL Database (MySQL 2008).Here is my C# Class:

 public static DataTable GetUnitByName(string unit)
    {
        Database db = DatabaseFactory.CreateDatabase();
        string sqlCommand = "GetUnitByName";
        DbCommand dbCommandWrapper = db.GetStoredProcCommand(sqlCommand);
        db.AddInParameter(dbCommandWrapper, "@Unit", DbType.String, unit);
        DataSet ds = db.ExecuteDataSet(dbCommandWrapper);
        DataTable dt = ds.Tables[0];
        return dt;
    }

And Here is the Stored Procedure :

alter PROCEDURE [dbo].[GetUnitByName]
(
@Unit nvarchar
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
    DECLARE @Err int
    -- Insert statements for procedure here
select * from Unit where Name=@Unit
   SET @Err = @@Error

    RETURN @Err
END

I call the Procedure Like this: DataTable unit1 = Unit.GetUnitByName(per100gText[1]enter code here); , wich per10gText is a string list. but it return empty datatable. the connection to sql server, in debuging i see that the procedure can't read the parameter. Any Help Please.

Dudi Konfino · Answer 1 · 2014-12-05T10:24:06.373

0

if you want proc to return param you need first to declare it as output param

alter PROCEDURE [dbo].[GetUnitByName]
(
@Unit nvarchar , @err int output
)
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 * from Unit where Name=@Unit
   SET @Err = @@Error

    RETURN @Err
END

edited Dec 05 '14 at 10:24

answered Dec 05 '14 at 08:28

Dudi Konfino

1,126
2
13
24

I don't use sql code often, can you explain more about function? and to return the value directly using sql and C#? – alim91 Dec 05 '14 at 08:53
i dont know c# -:( read this about proc and func:http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server – Dudi Konfino Dec 05 '14 at 08:58

Send string Parameter to Stored Procedure

1 Answers1