--sql code Geovin Du
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_AssessmentProjectSetSearch')
DROP PROCEDURE proc_Select_AssessmentProjectSetSearch
GO
CREATE PROCEDURE proc_Select_AssessmentProjectSetSearch
(
@AssessmentProjectSetJobType Uniqueidentifier, --考核職位類型
@AssessmentProjectSetUidKey nvarchar(2000) output --考核項目
)
AS
declare @sql nvarchar(4000),@where nvarchar(4000)--,@AssessmentProjectSetUidKey nvarchar(4000)
set @where=''
select @AssessmentProjectSetUidKey=cast(AssessmentProjectSetUidKey as nvarchar(4000)) from AssessmentProjectSet where AssessmentProjectSetJobType=@AssessmentProjectSetJobType
set @sql='SELECT * FROM AssessmentProjectList where AssessmentProjectID in('
if(@AssessmentProjectSetUidKey IS null or @AssessmentProjectSetUidKey='')
begin
set @AssessmentProjectSetUidKey='1'
SET @where=@AssessmentProjectSetUidKey
end
else
begin
set @where=@AssessmentProjectSetUidKey
end
--print @AssessmentProjectSetUidKey
set @sql=@sql+@where+')'
--print @sql
exec (@sql)
GO
--測試
declare @s nvarchar(2220)
exec proc_Select_AssessmentProjectSetSearch '7606598a-34da-43e3-a107-950ae724b80d',@s output
select @s
---
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_AssessmentProjectSetReturnSearch')
DROP PROCEDURE proc_Select_AssessmentProjectSetReturnSearch
GO
CREATE PROCEDURE proc_Select_AssessmentProjectSetReturnSearch
(
@AssessmentProjectSetJobType Uniqueidentifier, --考核職位類型
@AssessmentProjectSetUidKey nvarchar(2000) output --考核項目
)
AS
declare @sql nvarchar(4000),@where nvarchar(4000),@return int --,@AssessmentProjectSetUidKey nvarchar(4000)
set @where=''
select @AssessmentProjectSetUidKey=cast(AssessmentProjectSetUidKey as nvarchar(4000)) from AssessmentProjectSet where AssessmentProjectSetJobType=@AssessmentProjectSetJobType
set @sql='SELECT * FROM AssessmentProjectList where AssessmentProjectID in('+@AssessmentProjectSetUidKey+')'
exec (@sql)
if(@@error>0)
set @return=2
else
set @return=1
return @return
--return @AssessmentProjectSetUidKey --C# 中操作不可以字符類型 SQL上可以查詢
GO
--測試
declare @s nvarchar(2220)
exec proc_Select_AssessmentProjectSetReturnSearch '330FEE4C-A17E-46A4-9460-CA4DEB782ADF',@s output
select @s
sharp code
/// <summary>
/// 塗聚文 20130119
/// 返回輸出參數
/// </summary>
/// <param name="assessmentProjectSetJobType"></param>
/// <param name="Return">輸出參數</param>
/// <returns></returns>
public DataTable SelectAssessmentProjectSetJobTypeDataTableSearch(Guid assessmentProjectSetJobType,out string Return)
{
DataTable dt = new DataTable();
try
{
// SqlParameter par = new SqlParameter("@AssessmentProjectSetJobType ", assessmentProjectSetJobType);
SqlParameter[] par = new SqlParameter[]{
//new SqlParameter("@AssessmentProjectSetID",SqlDbType.Int,4),
new SqlParameter("@AssessmentProjectSetJobType",SqlDbType.UniqueIdentifier),
new SqlParameter("@AssessmentProjectSetUidKey",SqlDbType.NVarChar,4000),
//new SqlParameter("@return",SqlDbType.Int,8),
};
par[0].Value = assessmentProjectSetJobType;
par[1].Direction = ParameterDirection.Output;
using (DataTable reader = DBHelper.GetTable("proc_Select_AssessmentProjectSetSearch", CommandType.StoredProcedure, par))
{
dt = reader;
Return =(string)par[1].Value.ToString();
}
}
catch (SqlException ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 塗聚文 20130119
/// 返回自定義參數
/// </summary>
/// <param name="assessmentProjectSetJobType"></param>
/// <param name="Return">自定義參數</param>
/// <returns></returns>
public DataTable SelectAssessmentProjectSetReturnDataTableSearch(Guid assessmentProjectSetJobType, out string Return)
{
DataTable dt = new DataTable();
try
{
// SqlParameter par = new SqlParameter("@AssessmentProjectSetJobType ", assessmentProjectSetJobType);
SqlParameter[] par = new SqlParameter[]{
//new SqlParameter("@AssessmentProjectSetID",SqlDbType.Int,4),
new SqlParameter("@AssessmentProjectSetJobType",SqlDbType.UniqueIdentifier),
new SqlParameter("@AssessmentProjectSetUidKey",SqlDbType.NVarChar,4000),
new SqlParameter("@return",SqlDbType.Int,8),
};
par[0].Value = assessmentProjectSetJobType;
par[1].Direction = ParameterDirection.Output;
par[2].Direction = ParameterDirection.ReturnValue;//ReturnValue返回值數值類型
using (DataTable reader = DBHelper.GetTable("proc_Select_AssessmentProjectSetReturnSearch", CommandType.StoredProcedure, par))
{
dt = reader;
Return = (string)par[1].Value.ToString();
int f = (int)par[2].Value;
}
}
catch (SqlException ex)
{
throw ex;
}
return dt;
}