Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more: , +
SQL
 ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250)
)
as 
declare @OgrenciKayitKontrol int
declare @Durum int 
declare @BelgeKayitKontrol int
declare @Sonuc int
Set @OgrenciKayitKontrol=(select count(*) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)
if(@OgrenciKayitKontrol=1)
begin
insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)
set @Durum=0
end
else
begin
set @Durum=1
end
Set @BelgeKayitKontrol=(select count(*) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)
if(@BelgeKayitKontrol=1)
begin
set @Sonuc=0
end
else
begin
set @Sonuc=1
end
return @Durum
return @Sonuc

but sonuc is null how can i do that 2 values returning in sp?
Posted
Updated 19-Jul-11 23:46pm
v2
Comments
Herman<T>.Instance 20-Jul-11 5:55am    
just a tip
Select count(*) is pretty expensive because it counts over all data while counting over a column is enough so select count(0) is enough

You can't return multiple values from stored procedure in the way you are doing it now. You can, however, specify your parameters to be OUTPUT so you can access them. See here[^] for additional explanation.
 
Share this answer
 
Comments
Herman<T>.Instance 20-Jul-11 5:53am    
or you do:
SELECT @Durum, @Sonuc in stead of return
Toniyo Jackson 20-Jul-11 5:59am    
Exactly +5
walterhevedeich 20-Jul-11 6:00am    
Thanks
Ccsnow 21-Jul-11 2:58am    
USE [SduBelgeler]
GO
/****** Object: StoredProcedure [dbo].[BelgeKayit] Script Date: 07/21/2011 09:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250),
@Durum int output,
@Sonuc int output
)
as
declare @OgrenciKayitKontrol int
declare @BelgeKayitKontrol int
Set @OgrenciKayitKontrol=(select count(0) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)
Set @BelgeKayitKontrol=(select count(0) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)
insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)
if(@OgrenciKayitKontrol=1)
begin
set @Durum=0
end
else
begin
set @Durum=1
end
return @Durum
if(@BelgeKayitKontrol=1)
begin
set @Sonuc=0
end
else
begin
set @Sonuc=1
end
return @Sonuc

ı always get @Sonuc result = 1 but ı dont understand it?

my VS2008 CODE İS

if (TxtOgrenciNo.Text == "" )
{
LabelDurum.Text = "Öğrenci Numarası boş bırakılamaz";
}
else
{
Baglanti baglan = new Baglanti();

SqlParameter[] paramdizi = new SqlParameter[6]
{
new SqlParameter("@OgrenciNo",SqlDbType.Int),
new SqlParameter("@BelgeId",SqlDbType.Int) ,
new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar) ,
new SqlParameter("@Durum",SqlDbType.Int) ,
new SqlParameter("@Sonuc",SqlDbType.Int) ,
};

paramdizi[0].Value = TxtOgrenciNo.Text;
paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
paramdizi[2].Value = TxtDosyaBaslik.Text;
paramdizi[3].Value = TxtAciklama.Text;
paramdizi[4].Direction = ParameterDirection.Output;
paramdizi[5].Direction = ParameterDirection.Output;

SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi);
List<belgegoster> belgeler = new List<belgegoster>();
while (dr.Read())
{
BelgeGoster belge = new BelgeGoster(dr.GetInt32(0), dr.GetInt32(1), dr.GetString(2), dr.GetString(3));
belgeler.Add(belge);

}
GridViewBelgeler.DataSource = belgeler;
GridViewBelgeler.DataBind();

if (paramdizi[4].Value.ToString() == "0")
{
LabelDurum.Text = " Kayıt eklendi ";
}
else
{
LabelDurum.Text = " Böyle bir öğrenci numarası bulunmamaktadır. ";
}

if (paramdizi[5].Value.ToString() == "0")
{
LabelDurum.Text = " mevzut yok ";
}
else
{
LabelDurum.Text = " Kayit mevcut ";
}


}
help pls
Ccsnow 21-Jul-11 3:04am    
i always take a massage " Kayit mevcut " i dont understand it?
Use output parameters, or return a select:
ALTER PROC Testy(@ID int, @OUT1 nvarchar(100) OUTPUT, @OUT2 nvarchar(100) OUTPUT)
AS
SET @OUT1 = (Select Customer FROM Customers WHERE Id=@ID)
SET @OUT2 = (SELECT [TEXT] from myTable WHERE iD=@ID)
return @ID

or
ALTER PROC Testy(@ID int)
AS
DECLARE @OUT1 nvarchar(100)
DECLARE @OUT2 nvarchar(100)
SET @OUT1 = (Select Customer FROM Customers WHERE Id=@ID)
SET @OUT2 = (SELECT [TEXT] from myTable WHERE iD=@ID)
SELECT @OUT1, @OUT2
 
Share this answer
 
SQL
@Durum int output,
@Sonuc int output



then in aspx page


SQL
sqlCmd.Parameters.Add("@Durum",SqlDbType.Int).Direction=ParameterDirection.Output;
DataTable dtTable=new datalayer().SelectSpData("Sp_Name",sqlCmd);
int Durum=Convert.ToInt32(sqlCmd.Parameters["@Durum"].value);


same code for Sonuc also..
I think this may help u..
 
Share this answer
 
v2
Comments
Ccsnow 21-Jul-11 2:59am    
USE [SduBelgeler]
GO
/****** Object: StoredProcedure [dbo].[BelgeKayit] Script Date: 07/21/2011 09:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250),
@Durum int output,
@Sonuc int output
)
as
declare @OgrenciKayitKontrol int
declare @BelgeKayitKontrol int
Set @OgrenciKayitKontrol=(select count(0) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)
Set @BelgeKayitKontrol=(select count(0) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)
insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)
if(@OgrenciKayitKontrol=1)
begin
set @Durum=0
end
else
begin
set @Durum=1
end
return @Durum
if(@BelgeKayitKontrol=1)
begin
set @Sonuc=0
end
else
begin
set @Sonuc=1
end
return @Sonuc

ı always get @Sonuc result = 1 but ı dont understand it?

my VS2008 CODE İS

if (TxtOgrenciNo.Text == "" )
{
LabelDurum.Text = "Öğrenci Numarası boş bırakılamaz";
}
else
{
Baglanti baglan = new Baglanti();

SqlParameter[] paramdizi = new SqlParameter[6]
{
new SqlParameter("@OgrenciNo",SqlDbType.Int),
new SqlParameter("@BelgeId",SqlDbType.Int) ,
new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar) ,
new SqlParameter("@Durum",SqlDbType.Int) ,
new SqlParameter("@Sonuc",SqlDbType.Int) ,
};

paramdizi[0].Value = TxtOgrenciNo.Text;
paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
paramdizi[2].Value = TxtDosyaBaslik.Text;
paramdizi[3].Value = TxtAciklama.Text;
paramdizi[4].Direction = ParameterDirection.Output;
paramdizi[5].Direction = ParameterDirection.Output;

SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi);
List<belgegoster> belgeler = new List<belgegoster>();
while (dr.Read())
{
BelgeGoster belge = new BelgeGoster(dr.GetInt32(0), dr.GetInt32(1), dr.GetString(2), dr.GetString(3));
belgeler.Add(belge);

}
GridViewBelgeler.DataSource = belgeler;
GridViewBelgeler.DataBind();

if (paramdizi[4].Value.ToString() == "0")
{
LabelDurum.Text = " Kayıt eklendi ";
}
else
{
LabelDurum.Text = " Böyle bir öğrenci numarası bulunmamaktadır. ";
}

if (paramdizi[5].Value.ToString() == "0")
{
LabelDurum.Text = " mevzut yok ";
}
else
{
LabelDurum.Text = " Kayit mevcut ";
}


}
help pls
I think you SQL is overly complicated as I believe this is logically identical

SQL
 ALTER proc [dbo].[BelgeKayit]
(
	@OgrenciNo int,
	@BelgeId int,
	@BelgeBaslik nvarchar(30),
	@BelgeAciklama nvarchar(250)
)

AS 
DECLARE @OgrenciKayitKontrol INT
DECLARE @Durum INT 

SELECT 
  @OgrenciKayitKontrol = COUNT(OgrenciNo) 
FROM 
  Ogrenci 
WHERE 
  Ogrenci.OgrenciNo = @OgrenciNo

IF(@OgrenciKayitKontrol = 1) BEGIN
	INSERT INTO OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) VALUES (@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)
	SET @Durum=0
END
ELSE BEGIN
	SET @Durum=1
END

SELECT @Durum
 
Share this answer
 
v2
use this and use a list to recieve return values.

C#
using (SqlConnection con = new SqlConnection(this.ConnectionString))
          {
              SqlCommand cmd = new SqlCommand("tbl_Visits_GetDetailsofTop5", con);
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
              cmd.Parameters.Add("@param1", System.Data.SqlDbType.DateTime).Value = param;
               con.Open();
              return function1(ExecuteReader(cmd));

          }
 
Share this answer
 
User paramete as Output

Search it on google
 
Share this answer
 
//in aspx page

OpenConnection();
           SqlCommand cmd = new SqlCommand("test", connection);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@UserName", UserName);
           cmd.Parameters.AddWithValue("@Password", Password);
           cmd.Parameters.AddWithValue("@mode", "signinlogin");
           SqlParameter p = new SqlParameter("@uname", SqlDbType.NVarChar, 1000);
           SqlParameter p1 = new SqlParameter("@name", SqlDbType.NVarChar, 1000);
           p.Direction = ParameterDirection.Output;
           p1.Direction = ParameterDirection.Output;
           cmd.Parameters.Add(p);
           cmd.Parameters.Add(p1);
           int INSID = cmd.ExecuteNonQuery();
           string Output = cmd.Parameters["@uname"].Value.ToString();
           string Output1 = cmd.Parameters["@name"].Value.ToString();


           UserDetails sbintValue = new UserDetails();
          
           CloseConnection();



//in sql

create procedure test
@mode varchar(20),
@username nvarchar(1000)=null,
@password varchar(max)=null,
@name nvarchar(1000) =null output,
@uname varchar(max) =null output

as


begin
if(@mode='signinlogin')
begin
select @name=Firstname,@uname=email from  tablename where password =@password and username=@username

end

end
 
Share this answer
 
Comments
CHill60 9-Aug-13 3:59am    
Not really that different to solutions 1 or 2 from 2 years ago

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900