sql server insert stored procedure with return type [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

Latest Posts