sql server insert stored procedure with return type [Resolved]

Posted by reddysankark-13471 under Sql Server on 9/21/2012 | Points: 10 | Views : 1539 | Status : [Member] | Replies : 3
hi

can any body give me insert stored procedure coding for this

insert class

public int InsertCountries()
{
try
{
SqlParameter[] p = new SqlParameter[10];
p[0] = new SqlParameter("@CountryName", Name);
p[1] = new SqlParameter("@CountryAbbr", Abbreviation);
p[2] = new SqlParameter("@CountryDesc", Description);
p[3] = new SqlParameter("@CountryMap", Map);
p[4] = new SqlParameter("@CountryMapFile", MapFile);
p[5] = new SqlParameter("@CountryFlag", Flag);
p[6] = new SqlParameter("@CountryFlagFile", FlagFile);
p[7] = new SqlParameter("@CountryVideo", Video);
p[8] = new SqlParameter("@CountryVideoFile", VideoFile);
p[9] = new SqlParameter("@Flag", SqlDbType.Int);
p[9].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spInsertCountries", p);
return Convert.ToInt32(p[9].Value);

}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}

aspx page

protected void BtnAdd_Click(object sender, EventArgs e)
{
try
{
objVisaType.Name = txtCountryName.Text.Trim();
objVisaType.Abbreviation = txtCountryAbbr.Text.Trim();
objVisaType.Description = txtCountryDesc.Text.Trim();
objVisaType.Map = (byte[])Session["Photo"];
objVisaType.MapFile = Session["FileName"].ToString();
objVisaType.Flag = (byte[])Session["FlagPhoto"];
objVisaType.FlagFile = Session["FlagFileName"].ToString();
objVisaType.Video = (byte[])Session["VideoFileContent"];
objVisaType.VideoFile = Session["VideoFileName"].ToString();

int Flag = objVisaType.InsertCountries();
if (Flag == 1)
{
Page.RegisterClientScriptBlock("Travel", "<script>alert('Country Inserted')</script>");
btnClear_Click(sender, e);
}
else if (Flag == 0)
{
Page.RegisterClientScriptBlock("Travel", "<script>alert('Bad Server Request, Try again')</script>");
btnClear_Click(sender, e);
}
else if (Flag == 2)
{
Page.RegisterClientScriptBlock("Travel", "<script>alert(Country Already Inserted')</script>");
}
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}

sankarreddy


Responses

Posted by: Tanujad on: 9/21/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
See the below example for writing a stored procedure with an output paramter and the way to execute that stored procedure --

CREATE PROCEDURE spInsertData

(
@param1 VARCHAR(55),
@param2 VARCHAR(55),
@Flag INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT 'True' FROM tableName WHERE col1 = @param1)
-- Assuming col1 is the PrimaryKey/unique in the table
BEGIN
SELECT @Flag = 2 -- Country Already Inserted
END
ELSE
BEGIN
INSERT INTO tableName (col1,col2) VALUES (@param1,@param2)

IF @@ERROR <> 0
BEGIN
SELECT @Flag = 0 -- Bad server Request
END

ELSE IF @@ERROR = 0
BEGIN
SELECT @Flag = 1 -- Country Inserted
END
END
END


-- Way to Execute the SP with putput parameter
DECLARE @FlagValue INT
EXEC spName,@Flag=@FlagValue OUTPUT



Please mark it as answer if it solves your query.

Thanks
Tanuja
(If this content helped you, Please mark it as Answer)

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
see this example code
Eg: Creating for Employee Records
Create Procedure Procedure name
(@action char(1),@Empid varchar(10),@Empname varchar(25),@Age int,@Sal int,@Address varchar(50)
as
begin
if(@action='i') //i=insert
insert into tblemp (@Empid,@Empname,@Age,@Sal,@Address) values(Empid,Empname,Age,Sal,Address)
end
begin
if(@action='u')//update
update tblemp @Emp=Emp and @Age=Age and @Sal=sal where @Empid=Empid
end
begin
if(@action='s')//select
Select * from tblemp
end

Thanks & Regards
Hari

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/21/2012 [Member] Starter | Points: 25

Up
0
Down
Please Mark As Answer if u satisfy with my answer

Thanks & Regards
Hari

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response