Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
 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 20-Jul-11 0:45am
Edited 20-Jul-11 0:46am
v2
Comments
digimanus at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
digimanus at 20-Jul-11 5:53am
   
or you do:
SELECT @Durum, @Sonuc in stead of return
Toniyo Jackson at 20-Jul-11 5:59am
   
Exactly +5
walterhevedeich at 20-Jul-11 6:00am
   
Thanks
Ahmet2009 at 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 belgeler = new List();
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
Ahmet2009 at 21-Jul-11 3:04am
   
i always take a massage " Kayit mevcut " i dont understand it?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

@Durum int output,
@Sonuc int output
 

then in aspx page
 

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..
  Permalink  
v2
Comments
Ahmet2009 at 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 belgeler = new List();
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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I think you SQL is overly complicated as I believe this is logically identical
 
 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
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

User paramete as Output
 
Search it on google
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

use this and use a list to recieve return values.
 
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));
 
          }
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

//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
  Permalink  
Comments
CHill60 at 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)



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 9 Aug 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100