//存储过程 CREATE PROCEDURE spTest AS BEGIN DECLARE @return_count int SELECT @return_count=COUNT(*) FROM tTest RETURN @return_count END //asp.net程序 using System; using System.Data; using System.SqlClient; public class dataConnection { public static void Main() { int number; SqlConnection conn=new SqlConnection(); conn.ConnectionString="Data Source=...."; conn.Open(); SqlCommand _command=new SqlCommand(); SqlParameter _parameter=new SqlParameter(); _parameter.ParameterName="@return_count"; _parameter.IsNullable=false; _parameter.SqlDbType=SqlDbType.Int; _parameter.Direction=ParameterDirection.ReturnVlaue; _command.Connection=conn; _command.CommandType=CommandType.StoredProcudure; _command.CommandText="spTest" _command.Parameters.Add(_parameter); number= _command.ExecuteScalar() Console.WriteLine(number); } } 可能有错误,你在改改;
btlxy说的对的.他这种方法可行
试试 using System; using System.Data.SqlClient;namespace ConsoleApplication1 { class Class1 { [STAThread] static void Main(string[] args) { String CNstring; CNstring="Data Source=.;Initial Catalog=pubs;User ID=sa; password=leoqin2003"; //改成你自己的连接 SqlConnection cn=new SqlConnection(CNstring);
try { cn.Open(); SqlCommand cmd = new SqlCommand("select count(*) as nn from employee",cn); Console.WriteLine( "count(*) from employee:{0}",cmd.ExecuteScalar().ToString()); cn.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } Console.ReadLine(); } } }
AS
SELECT count(*) num FROM tTest-- 调用
exec spTest
Create Predure spTest
ASReturn SELECT count(*) FROM tTest
-----------------------------------
在SQLServer查询分析器中执行此spTest,怎样写能得到表tTest的行数的返回值?
??? EXEC spTest ???
@ret_value integer output
ASReturn
select @ret_value =(SELECT count(*) FROM tTest)
Create Predure spTest
@ret_value integer output
AS
select @ret_value =(SELECT count(*) FROM tTest)
as
declare @ret_value integer
AS
SELECT count(*) FROM tTest
return @@rowcountgo
Return 1
我在前台用什么方式能得到这个1 。
------------------------------------------------------------------------------------
回:happyflystone(快乐的飞石)不是在存储过程中取返回值,所以可能不能用T_SQL的全局变量。
这种做法是正确的。
AS
exec('select count(*) FROM 表')or
Create PROCEDURE spTest
AS
select count(*) FROM 表返回是个记录集了
ASSELECT count(*) FROM tTest
Int32 count = (int32) cmd.ExecuteScalar();如果是调用存贮过程的话,修改CommandText和CommandType
CREATE PROCEDURE spTest
AS
BEGIN
DECLARE @return_count int
SELECT @return_count=COUNT(*)
FROM tTest
RETURN @return_count
END
//asp.net程序
using System;
using System.Data;
using System.SqlClient;
public class dataConnection
{
public static void Main()
{
int number;
SqlConnection conn=new SqlConnection();
conn.ConnectionString="Data Source=....";
conn.Open();
SqlCommand _command=new SqlCommand();
SqlParameter _parameter=new SqlParameter();
_parameter.ParameterName="@return_count";
_parameter.IsNullable=false;
_parameter.SqlDbType=SqlDbType.Int;
_parameter.Direction=ParameterDirection.ReturnVlaue;
_command.Connection=conn;
_command.CommandType=CommandType.StoredProcudure;
_command.CommandText="spTest"
_command.Parameters.Add(_parameter);
number= _command.ExecuteScalar()
Console.WriteLine(number);
}
}
可能有错误,你在改改;
using System;
using System.Data.SqlClient;namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
String CNstring;
CNstring="Data Source=.;Initial Catalog=pubs;User ID=sa; password=leoqin2003"; //改成你自己的连接
SqlConnection cn=new SqlConnection(CNstring);
try
{
cn.Open();
SqlCommand cmd = new SqlCommand("select count(*) as nn from employee",cn);
Console.WriteLine( "count(*) from employee:{0}",cmd.ExecuteScalar().ToString());
cn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.ReadLine();
}
}
}
public int UserAdd(string UserName,string LoginName,string PassWord,int Sex, string HeadShip,string UserIP,object ActUserID)
{
SqlConnection sqlConn;
SqlCommand sqlCmd;
SqlParameter sqlParm; string strTemp;
int status; //打开连接
sqlConn=new SqlConnection ();
sqlConn.ConnectionString = SystemConfig.ApplicationConfig.GetDataBaseConnection();
sqlConn.Open (); //存储过程
sqlCmd=new SqlCommand ();
sqlCmd.CommandType =CommandType.StoredProcedure ;
sqlCmd.CommandText ="up_AddUser";
sqlCmd.Connection =sqlConn; //加载参数
sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@UserName";
sqlParm.Value =UserName;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.String;
sqlParm.Size =50;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@LoginName";
sqlParm.Value =LoginName;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.String;
sqlParm.Size =50;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@PassWord";
sqlParm.Value =PassWord;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.String;
sqlParm.Size =50;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@Sex";
sqlParm.Value =Sex;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.Int32;
sqlParm.Size =4;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@HeadShip";
sqlParm.Value =HeadShip;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.String;
sqlParm.Size =50;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@UserIP";
sqlParm.Value =UserIP;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.String;
sqlParm.Size =20;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null; sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@ActUserID";
sqlParm.Value =ActUserID;
sqlParm.Direction =ParameterDirection.Input ;
sqlParm.DbType =DbType.Int32;
sqlParm.Size =4;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null;
//这是接受输出参数
sqlParm=new SqlParameter ();
sqlParm.ParameterName ="@ReturnValue";
sqlParm.Direction =ParameterDirection.Output ;
sqlParm.DbType =DbType.Int32;
sqlCmd.Parameters.Add (sqlParm);
sqlParm=null;
sqlCmd.ExecuteNonQuery ();
strTemp=sqlCmd.Parameters["@ReturnValue"].Value.ToString ();
if (strTemp!=null)
{
status=System.Int32.Parse(strTemp);
}
else
{
status= -2;
}
ProjectTest.SystemConfig.ApplicationConfig.clear(sqlCmd,sqlConn);
return status;
}
/存储过程/
/* 增加系统操作员 */
CREATE PROCEDURE [DBO].[up_AddUser]
@UserName Varchar(50), /* 用户名称*/
@LoginName Varchar(50), /* 登录名称*/
@PassWord Varchar(50), /* 口令 */
@Sex INT, /* 性别 =0 男;=1 女 */
@HeadShip Varchar(50), /* 职务*/
@UserIP Varchar(20), /* 当前登录用户IP */
@ActUserID INT, /* 当前登录用户ID */
@ReturnValue INT OUTPUT /* 输出参数,=0 用户名错误,=-1 密码错误 */
AS
Declare @UserID INT /* 用户ID */
IF EXISTS ( SELECT * FROM LoginUser Where UPPER(LoginName) = UPPER(@LoginName) )
BEGIN
SET @ReturnValue = -1 /* 登录名重复 */
RETURN
END
ELSE
BEGIN
INSERT INTO LoginUser( UserName, LoginName, [PassWord], Sex, HeadShip )
VALUES( @UserName, @LoginName, @PassWord , @Sex, @HeadShip) /* 加入到操作日志 */
INSERT INTO ActionLog( UserID, ActDateTime, ActioinContent , UserIP)
VALUES( @ActUserID, GetDate(), '添加' + @UserName + '用户' , @UserIP)
SET @ReturnValue = 0
RETURN
END
GO