- --自定义函数
- create function fun_result(@score int)
- returns varchar(10)
- as
- begin
- declare @result varchar(10)
- if @score>=90
- set @result='优秀'
- else if @score>=80 and @score<90
- set @result='良好'
- else if @score>=60 and @score<80
- set @result='及格'
- else
- set @result='不及格'
- return @result
- end
- go
- create function fun_result(@score int)
- returns varchar(10)
- as
- begin
- declare @result varchar(10)
- select @result=case when @score>=90 then '优秀'
- when @score>=80 and @score<90 then '良好'
- when @score>=60 and @score<80 then '及格'
- else '不及格' end
- return @result
- end
- go
- --执行
- select dbo.fun_result(50)
- select case stuSex when '1' then '男'
- when '2' then '女'
- else '其他' end as '性别',COUNT(*) as '数量'
- from student group by stuSex
- --找出empId有2个以上的数据
- select empId from Tab_Extra_Salary group by empId having COUNT(*)>1
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 --SQL自定义函数:
CREATE FUNCTION [GetProjectID] (@headStr nvarchar(10),@date datetime)
)
RETURNS NVARCHAR(200)
AS
BEGIN
--不能在自定义函数中用INSERT INTO
--insert into emos_cust(cust_name,dates)values(
--@headStr,@date
--)
return 'TEST BY HANSHU'
END
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 --SQL自定义函数:
CREATE FUNCTION [GetProjectID] (@headStr nvarchar(10),@date datetime)
)
RETURNS NVARCHAR(200)
AS
BEGIN
--不能在自定义函数中用INSERT INTO
--insert into emos_cust(cust_name,dates)values(
--@headStr,@date
--)
return 'TEST BY HANSHU'
END
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 /// <summary>
/// 获取项目文件编号 geovindu@163.com 涂聚文
/// </summary>
private void FileNo()
{
SqlConnection conn = new SqlConnection(connectionString);
string strSql = "GetProjectID"; //自定SQL函數
SqlCommand cmd = new SqlCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@headStr", SqlDbType.NVarChar).Value = "ZQ3"; //輸入參數
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = System.DateTime.Now.ToShortDateString(); //輸入參數
cmd.Parameters.Add("@returnString", SqlDbType.NVarChar);
cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue; //返回參數
try
{
conn.Open();
object o= cmd.ExecuteScalar();
this.txtAFileNO.Text = cmd.Parameters["@returnString"].Value.ToString();
//Response.Write("");
}
catch (Exception ex)
{
this.txtAFileNO.Text = ex.Message;
}
finally
{
if (!(conn.State == ConnectionState.Closed))
{
conn.Close();
}
}
}
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 /// <summary>
/// 获取项目文件编号 geovindu@163.com 涂聚文
/// </summary>
private void FileNo()
{
SqlConnection conn = new SqlConnection(connectionString);
string strSql = "GetProjectID"; //自定SQL函數
SqlCommand cmd = new SqlCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@headStr", SqlDbType.NVarChar).Value = "ZQ3"; //輸入參數
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = System.DateTime.Now.ToShortDateString(); //輸入參數
cmd.Parameters.Add("@returnString", SqlDbType.NVarChar);
cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue; //返回參數
try
{
conn.Open();
object o= cmd.ExecuteScalar();
this.txtAFileNO.Text = cmd.Parameters["@returnString"].Value.ToString();
//Response.Write("");
}
catch (Exception ex)
{
this.txtAFileNO.Text = ex.Message;
}
finally
{
if (!(conn.State == ConnectionState.Closed))
{
conn.Close();
}
}
}