Sunday, May 18, 2014

Get multiple output parameters by comma separated in sql and c#

1) C# Method

 public string AddLead(string userName, string firstName, string middleName, string lastName, string phone, string email, string profession, string categoryId, string refferedby, string leadstatusId, string birthDate, string maritalStatus, string notes)
        {
            try
            {
                using (SqlCommand cmd = DBHelper.DBHelper.Instance.GetCommand("LeadMgmtAdd", CommandType.StoredProcedure))
                {
                    cmd.Parameters.AddWithValue("@userName", userName);
                    cmd.Parameters.AddWithValue("@firstName", firstName);
                    cmd.Parameters.AddWithValue("@middleName", middleName);
                    cmd.Parameters.AddWithValue("@lastName", lastName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@profession", profession);
                    cmd.Parameters.AddWithValue("@categoryId", categoryId);
                    cmd.Parameters.AddWithValue("@refferedby", refferedby);
                    cmd.Parameters.AddWithValue("@leadstatusId", leadstatusId);
                    cmd.Parameters.AddWithValue("@birthDate", birthDate);
                    cmd.Parameters.AddWithValue("@maritalStatus", maritalStatus);
                    cmd.Parameters.AddWithValue("@notes", notes);
                    var outParam = new SqlParameter("@Result", SqlDbType.NVarChar);
                    outParam.Direction = ParameterDirection.Output;
                    outParam.Size = 100;
                    //  cmd.Parameters["@Result"].Direction = ParameterDirection.Output;


                    cmd.Parameters.Add(outParam);
                    cmd.ExecuteNonQuery();
                    Result = cmd.Parameters["@Result"].Value.ToString();
                    DBHelper.DBHelper.Instance.CloseConnection(cmd);
                }
            }
            catch (Exception ex)
            {
                Result = ex.Message;
            }
            return Result;
        }

2) SQL

USE [Valweave]
GO
/****** Object:  StoredProcedure [dbo].[LeadMgmtAdd]    Script Date: 05/19/2014 11:00:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR: <VINOD SONJE>
-- CREATE DATE: <24/AUG/2011>
-- DESCRIPTION: <THIS STORE PROCEDURE MANAGE ADD UPDATE & DELETE FUNCTIONALITY FOR USER MASTER>
-- =============================================
ALTER PROCEDURE [dbo].[LeadMgmtAdd]

@userName nvarchar(max),
@firstName nvarchar(max),
@middleName nvarchar(max),
@lastName nvarchar(max),
@phone nvarchar(max),
@email nvarchar(max),
@profession nvarchar(max),
@categoryId nvarchar(max),
@refferedby nvarchar(max),
@leadstatusId nvarchar(max),
@birthDate nvarchar(max),
@maritalStatus nvarchar(max),
@notes nvarchar(max),
@RESULT nVARCHAR(max) OUTPUT

AS
BEGIN
 
BEGIN
INSERT INTO Lead(UserName)
VALUES(@userName)

SET @RESULT =(SELECT CONVERT(NVARCHAR, LeadId) + ',' + Name
FROM  Lead WHERE (Email = @email))

END


END

No comments:

Post a Comment